Practice_SQLite_From_Scratch.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "id": "87a85ab9",
      "metadata": {
        "id": "87a85ab9"
      },
      "source": [
        "# Practice-SQLite-From-Scratch"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "389dbb73",
      "metadata": {
        "id": "389dbb73"
      },
      "source": [
        "## Goal"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "57c2fe13",
      "metadata": {
        "id": "57c2fe13"
      },
      "source": [
        "The Goal of this notebook is to provide an environment based on Jupyter Notebooks (in particular Colab) to practice with the SQL language using SQLite"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "6ef09117",
      "metadata": {
        "id": "6ef09117"
      },
      "source": [
        "## Install SQLite (Colab)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "277b3fb8",
      "metadata": {
        "id": "277b3fb8"
      },
      "source": [
        "In Google Colab, based on linux container need to be installed, run the following cell"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 1,
      "id": "aSUr5vnErn77",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "aSUr5vnErn77",
        "outputId": "645b7f07-fa3a-45f1-9995-a528d0780280"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Reading package lists... Done\n",
            "Building dependency tree... Done\n",
            "Reading state information... Done\n",
            "Suggested packages:\n",
            "  sqlite3-doc\n",
            "The following NEW packages will be installed:\n",
            "  sqlite3\n",
            "0 upgraded, 1 newly installed, 0 to remove and 35 not upgraded.\n",
            "Need to get 768 kB of archives.\n",
            "After this operation, 1,873 kB of additional disk space will be used.\n",
            "Get:1 http://archive.ubuntu.com/ubuntu jammy-updates/main amd64 sqlite3 amd64 3.37.2-2ubuntu0.3 [768 kB]\n",
            "Fetched 768 kB in 1s (1,162 kB/s)\n",
            "Selecting previously unselected package sqlite3.\n",
            "(Reading database ... 121749 files and directories currently installed.)\n",
            "Preparing to unpack .../sqlite3_3.37.2-2ubuntu0.3_amd64.deb ...\n",
            "Unpacking sqlite3 (3.37.2-2ubuntu0.3) ...\n",
            "Setting up sqlite3 (3.37.2-2ubuntu0.3) ...\n",
            "Processing triggers for man-db (2.10.2-1) ...\n"
          ]
        }
      ],
      "source": [
        "!apt-get -y install sqlite3"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "L_1j75ilr30w",
      "metadata": {
        "id": "L_1j75ilr30w"
      },
      "source": [
        "Let's check if it's installed using the command line"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 2,
      "id": "576b7fd1",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "576b7fd1",
        "outputId": "406f4ec8-d90d-454f-8134-9eb21a14d606"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "3.37.2 2022-01-06 13:25:41 872ba256cbf61d9290b571c0e6d82a20c224ca3ad82971edc46b29818d5dalt1\n"
          ]
        }
      ],
      "source": [
        "!sqlite3 -version"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "2198ded0",
      "metadata": {
        "id": "2198ded0"
      },
      "source": [
        "## Install ipython magic\n",
        "Add the %sql and %%sql that allow to connect and run queries in Jupyter cell\n",
        "\n",
        "NB Colab installs by default this package, version prior to 0.5.0 may have some issue"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 3,
      "id": "9de69f82",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "9de69f82",
        "outputId": "c3fcf602-5a16-4bfd-f36f-1d11aa68ceb8",
        "scrolled": true
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Requirement already satisfied: ipython-sql in /usr/local/lib/python3.10/dist-packages (0.5.0)\n",
            "Requirement already satisfied: prettytable in /usr/local/lib/python3.10/dist-packages (from ipython-sql) (3.9.0)\n",
            "Requirement already satisfied: ipython in /usr/local/lib/python3.10/dist-packages (from ipython-sql) (7.34.0)\n",
            "Requirement already satisfied: sqlalchemy>=2.0 in /usr/local/lib/python3.10/dist-packages (from ipython-sql) (2.0.27)\n",
            "Requirement already satisfied: sqlparse in /usr/local/lib/python3.10/dist-packages (from ipython-sql) (0.4.4)\n",
            "Requirement already satisfied: six in /usr/local/lib/python3.10/dist-packages (from ipython-sql) (1.16.0)\n",
            "Requirement already satisfied: ipython-genutils in /usr/local/lib/python3.10/dist-packages (from ipython-sql) (0.2.0)\n",
            "Requirement already satisfied: typing-extensions>=4.6.0 in /usr/local/lib/python3.10/dist-packages (from sqlalchemy>=2.0->ipython-sql) (4.9.0)\n",
            "Requirement already satisfied: greenlet!=0.4.17 in /usr/local/lib/python3.10/dist-packages (from sqlalchemy>=2.0->ipython-sql) (3.0.3)\n",
            "Requirement already satisfied: setuptools>=18.5 in /usr/local/lib/python3.10/dist-packages (from ipython->ipython-sql) (67.7.2)\n",
            "Collecting jedi>=0.16 (from ipython->ipython-sql)\n",
            "  Downloading jedi-0.19.1-py2.py3-none-any.whl (1.6 MB)\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m1.6/1.6 MB\u001b[0m \u001b[31m9.8 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[?25hRequirement already satisfied: decorator in /usr/local/lib/python3.10/dist-packages (from ipython->ipython-sql) (4.4.2)\n",
            "Requirement already satisfied: pickleshare in /usr/local/lib/python3.10/dist-packages (from ipython->ipython-sql) (0.7.5)\n",
            "Requirement already satisfied: traitlets>=4.2 in /usr/local/lib/python3.10/dist-packages (from ipython->ipython-sql) (5.7.1)\n",
            "Requirement already satisfied: prompt-toolkit!=3.0.0,!=3.0.1,<3.1.0,>=2.0.0 in /usr/local/lib/python3.10/dist-packages (from ipython->ipython-sql) (3.0.43)\n",
            "Requirement already satisfied: pygments in /usr/local/lib/python3.10/dist-packages (from ipython->ipython-sql) (2.16.1)\n",
            "Requirement already satisfied: backcall in /usr/local/lib/python3.10/dist-packages (from ipython->ipython-sql) (0.2.0)\n",
            "Requirement already satisfied: matplotlib-inline in /usr/local/lib/python3.10/dist-packages (from ipython->ipython-sql) (0.1.6)\n",
            "Requirement already satisfied: pexpect>4.3 in /usr/local/lib/python3.10/dist-packages (from ipython->ipython-sql) (4.9.0)\n",
            "Requirement already satisfied: wcwidth in /usr/local/lib/python3.10/dist-packages (from prettytable->ipython-sql) (0.2.13)\n",
            "Requirement already satisfied: parso<0.9.0,>=0.8.3 in /usr/local/lib/python3.10/dist-packages (from jedi>=0.16->ipython->ipython-sql) (0.8.3)\n",
            "Requirement already satisfied: ptyprocess>=0.5 in /usr/local/lib/python3.10/dist-packages (from pexpect>4.3->ipython->ipython-sql) (0.7.0)\n",
            "Installing collected packages: jedi\n",
            "Successfully installed jedi-0.19.1\n"
          ]
        }
      ],
      "source": [
        "%pip install ipython-sql"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 4,
      "id": "SApIuYPmlej4",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "SApIuYPmlej4",
        "outputId": "9b873adc-208f-4296-dc55-7896b6e9b54f"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "SQLAlchemy                       2.0.27\n"
          ]
        }
      ],
      "source": [
        "!pip list installed | grep -i alchem"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 5,
      "id": "f8a666f7",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "f8a666f7",
        "outputId": "81638ef1-46c7-43bd-9525-cd90c12c2a39"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "ipython-sql                      0.5.0\n"
          ]
        }
      ],
      "source": [
        "!pip list installed | grep -i ipython-sql"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "1fa7be3d",
      "metadata": {
        "id": "1fa7be3d"
      },
      "source": [
        "### Load the SQL Magic extension"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 6,
      "id": "3822b0b1",
      "metadata": {
        "id": "3822b0b1"
      },
      "outputs": [],
      "source": [
        "%load_ext sql"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "9a97fe9d",
      "metadata": {
        "id": "9a97fe9d"
      },
      "source": [
        "## Create a directory to store dataset"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 7,
      "id": "980a9c33",
      "metadata": {
        "id": "980a9c33"
      },
      "outputs": [],
      "source": [
        "# Create Directory\n",
        "!mkdir -p datasets/"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "e9448237",
      "metadata": {
        "id": "e9448237"
      },
      "source": [
        "## Create a database"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "62d5e145",
      "metadata": {
        "id": "62d5e145"
      },
      "source": [
        "- In SQLite creating a database it's automatic when connecting.\n",
        "- We will use a database named \"sqlite-scratch.db\"\n",
        "- For reproducibility and educational purpose of this notebook we will delete any existing copy of the database, if you want to keep your previous work download the the file and upload again in the dataset directory without running the next cell"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 8,
      "id": "5ba1fd64",
      "metadata": {
        "id": "5ba1fd64"
      },
      "outputs": [],
      "source": [
        "# Delete\n",
        "!rm -f datasets/sqlite-scratch.db"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "81764564",
      "metadata": {
        "id": "81764564"
      },
      "source": [
        "## Connect to the database"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 9,
      "id": "ab692554",
      "metadata": {
        "id": "ab692554"
      },
      "outputs": [],
      "source": [
        "%sql sqlite:///datasets/sqlite-scratch.db"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "y8AvqyYSwoCe",
      "metadata": {
        "id": "y8AvqyYSwoCe"
      },
      "source": [
        "Let's check if we are connected, just using the %sql magic commands"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 10,
      "id": "F5-_771ZwuB6",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "F5-_771ZwuB6",
        "outputId": "e1aa3dc6-63fa-4286-cd0b-81693c35f6b3"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///datasets/sqlite-scratch.db\n"
          ]
        }
      ],
      "source": [
        "%sql"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "6c9d2f2b",
      "metadata": {
        "id": "6c9d2f2b"
      },
      "source": [
        "## Create Tables"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "80df130d",
      "metadata": {
        "id": "80df130d"
      },
      "source": [
        "*Exercise*: Create a table \"hogwarts_students\" with columns (id, name, age). Set id as primary key as column constraint"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 11,
      "id": "f9705d0e",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "f9705d0e",
        "outputId": "b2f79eab-42a3-4305-85b0-2d9f1421ced8"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///datasets/sqlite-scratch.db\n",
            "Done.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {},
          "execution_count": 11
        }
      ],
      "source": [
        "%%sql\n",
        "CREATE TABLE IF NOT EXISTS hogwarts_students(\n",
        "id   INTEGER PRIMARY KEY NOT NULL,\n",
        "name TEXT    NOT NULL,\n",
        "age  INTEGER\n",
        ");"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "ada66d00",
      "metadata": {
        "id": "ada66d00"
      },
      "source": [
        "## SQLite schema\n",
        "- SQLite stores database schema in a table ```sqlite_master```\n",
        "- To view it, simply run a SELECT query\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 15,
      "id": "dec050ee",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 75
        },
        "id": "dec050ee",
        "outputId": "2857470f-8c7d-4546-c923-1f6131d9f14d"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///datasets/sqlite-scratch.db\n",
            "Done.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[]"
            ],
            "text/html": [
              "<table>\n",
              "    <thead>\n",
              "        <tr>\n",
              "            <th>type</th>\n",
              "            <th>name</th>\n",
              "            <th>tbl_name</th>\n",
              "            <th>rootpage</th>\n",
              "            <th>sql</th>\n",
              "        </tr>\n",
              "    </thead>\n",
              "    <tbody>\n",
              "    </tbody>\n",
              "</table>"
            ]
          },
          "metadata": {},
          "execution_count": 15
        }
      ],
      "source": [
        "%%sql\n",
        "select * from sqlite_master"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "05738a9f",
      "metadata": {
        "id": "05738a9f"
      },
      "source": [
        "## DROP Table"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "b71bc6e3",
      "metadata": {
        "id": "b71bc6e3"
      },
      "source": [
        "*Exercise*: Drop table hogwarts_students"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 16,
      "id": "16470ae9",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "16470ae9",
        "outputId": "f9e8dd12-902f-4e7b-b8d6-aef89293c754"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///datasets/sqlite-scratch.db\n",
            "Done.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {},
          "execution_count": 16
        }
      ],
      "source": [
        "%%sql\n",
        "DROP table IF exists hogwarts_students"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "e4e828ee",
      "metadata": {
        "id": "e4e828ee"
      },
      "source": [
        "## Create table (table constraints)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "b1172775",
      "metadata": {
        "id": "b1172775"
      },
      "source": [
        "*Exercise*: Create a table \"students\" with columns (id, name, age). Set id as primary key as table constraint"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 17,
      "id": "hks-eCuSm4r5",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "hks-eCuSm4r5",
        "outputId": "c9101f4a-c4fc-4942-dacd-a5ade8903d6b"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///datasets/sqlite-scratch.db\n",
            "Done.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {},
          "execution_count": 17
        }
      ],
      "source": [
        "%%sql\n",
        "CREATE TABLE IF NOT EXISTS hogwarts_students(\n",
        "    id     INTEGER NOT NULL,\n",
        "    name   TEXT    NOT NULL,\n",
        "    age    INTEGER,\n",
        "    PRIMARY KEY(id)\n",
        ")"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "4deac32e",
      "metadata": {
        "id": "4deac32e"
      },
      "source": [
        "### Create table with foreign key\n",
        "- Foreign Key can be declared explicitly both as column or table constraint\n",
        "- They are not \"required\", relationship exist also without declaring, but it's a good practice\n",
        "- FK can also manage action triggered by the source tuple (i.e. on DELETE/UPDATE -> CASCADE/DELETE)\n",
        "\n",
        "Syntax\n",
        "```sql\n",
        "column datatype references references foreigntable(column)\n",
        "```\n",
        "[Full syntax](https://www.sqlite.org/syntax/foreign-key-clause.html)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "fd207ee4",
      "metadata": {
        "id": "fd207ee4"
      },
      "source": [
        "*Exercise*: Create the following relations explicitly defining references between hogwarts_students.house_id and hogwarts_houses.id\n",
        "- hogwarts_houses(id, name)\n",
        "- hogwarts_students(id, name, surname, age, house_id)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 18,
      "id": "90986153",
      "metadata": {
        "id": "90986153",
        "outputId": "6da191f1-8609-476b-f211-c228b380f5db",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///datasets/sqlite-scratch.db\n",
            "Done.\n",
            "Done.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {},
          "execution_count": 18
        }
      ],
      "source": [
        "%%sql\n",
        "DROP TABLE IF EXISTS hogwarts_houses;\n",
        "DROP TABLE IF EXISTS hogwarts_students;"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 19,
      "id": "2a8cc6da",
      "metadata": {
        "id": "2a8cc6da",
        "outputId": "aede46e4-63c1-457f-d9bc-de96dbda7c8f",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///datasets/sqlite-scratch.db\n",
            "Done.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {},
          "execution_count": 19
        }
      ],
      "source": [
        "%%sql\n",
        "CREATE TABLE IF NOT EXISTS hogwarts_houses(\n",
        "id   INTEGER PRIMARY KEY NOT NULL,\n",
        "name TEXT    NOT NULL\n",
        ");"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 20,
      "id": "7c0f3929",
      "metadata": {
        "id": "7c0f3929",
        "outputId": "9d867524-08c1-4793-8b03-a5dea5eca2c3",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///datasets/sqlite-scratch.db\n",
            "Done.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {},
          "execution_count": 20
        }
      ],
      "source": [
        "%%sql\n",
        "CREATE TABLE IF NOT EXISTS hogwarts_students(\n",
        "id   INTEGER PRIMARY KEY NOT NULL,\n",
        "name TEXT    NOT NULL,\n",
        "age  INTEGER,\n",
        "house_id REFERENCES hogwarts_houses(id)\n",
        ");"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 21,
      "id": "fe9c2de0",
      "metadata": {
        "id": "fe9c2de0",
        "outputId": "f88f01a0-6dbd-46fa-ef49-6d5b1210bb0d",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 256
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///datasets/sqlite-scratch.db\n",
            "Done.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[('table', 'hogwarts_houses', 'hogwarts_houses', 2, 'CREATE TABLE hogwarts_houses(\\nid   INTEGER PRIMARY KEY NOT NULL,\\nname TEXT    NOT NULL\\n)'),\n",
              " ('table', 'hogwarts_students', 'hogwarts_students', 3, 'CREATE TABLE hogwarts_students(\\nid   INTEGER PRIMARY KEY NOT NULL,\\nname TEXT    NOT NULL,\\nage  INTEGER,\\nhouse_id REFERENCES hogwarts_houses(id)\\n)')]"
            ],
            "text/html": [
              "<table>\n",
              "    <thead>\n",
              "        <tr>\n",
              "            <th>type</th>\n",
              "            <th>name</th>\n",
              "            <th>tbl_name</th>\n",
              "            <th>rootpage</th>\n",
              "            <th>sql</th>\n",
              "        </tr>\n",
              "    </thead>\n",
              "    <tbody>\n",
              "        <tr>\n",
              "            <td>table</td>\n",
              "            <td>hogwarts_houses</td>\n",
              "            <td>hogwarts_houses</td>\n",
              "            <td>2</td>\n",
              "            <td>CREATE TABLE hogwarts_houses(<br>id   INTEGER PRIMARY KEY NOT NULL,<br>name TEXT    NOT NULL<br>)</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>table</td>\n",
              "            <td>hogwarts_students</td>\n",
              "            <td>hogwarts_students</td>\n",
              "            <td>3</td>\n",
              "            <td>CREATE TABLE hogwarts_students(<br>id   INTEGER PRIMARY KEY NOT NULL,<br>name TEXT    NOT NULL,<br>age  INTEGER,<br>house_id REFERENCES hogwarts_houses(id)<br>)</td>\n",
              "        </tr>\n",
              "    </tbody>\n",
              "</table>"
            ]
          },
          "metadata": {},
          "execution_count": 21
        }
      ],
      "source": [
        "%%sql\n",
        "select * from sqlite_master"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "968208ab",
      "metadata": {
        "id": "968208ab"
      },
      "source": [
        "# Display Physical Schema\n",
        "\n",
        "- graphviz\n",
        "- https://github.com/innovationOUtside/ipython_magic_sqlalchemy_schemadisplay"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 22,
      "id": "3870b549",
      "metadata": {
        "id": "3870b549",
        "outputId": "03fd8af4-4e31-4cce-ee24-9e7d8f4092e6",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Reading package lists... Done\n",
            "Building dependency tree... Done\n",
            "Reading state information... Done\n",
            "graphviz is already the newest version (2.42.2-6).\n",
            "0 upgraded, 0 newly installed, 0 to remove and 35 not upgraded.\n"
          ]
        }
      ],
      "source": [
        "!apt-get -y install graphviz"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 23,
      "id": "c6ad464b",
      "metadata": {
        "id": "c6ad464b",
        "outputId": "82766cd5-c77f-40b8-cc32-2849d5d8c2ea",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Collecting schemadisplay-magic\n",
            "  Downloading schemadisplay_magic-0.0.8-py3-none-any.whl (10 kB)\n",
            "Requirement already satisfied: pandas in /usr/local/lib/python3.10/dist-packages (from schemadisplay-magic) (1.5.3)\n",
            "Requirement already satisfied: sqlalchemy in /usr/local/lib/python3.10/dist-packages (from schemadisplay-magic) (2.0.27)\n",
            "Requirement already satisfied: pydot in /usr/local/lib/python3.10/dist-packages (from schemadisplay-magic) (1.4.2)\n",
            "Requirement already satisfied: graphviz in /usr/local/lib/python3.10/dist-packages (from schemadisplay-magic) (0.20.1)\n",
            "Requirement already satisfied: python-dateutil>=2.8.1 in /usr/local/lib/python3.10/dist-packages (from pandas->schemadisplay-magic) (2.8.2)\n",
            "Requirement already satisfied: pytz>=2020.1 in /usr/local/lib/python3.10/dist-packages (from pandas->schemadisplay-magic) (2023.4)\n",
            "Requirement already satisfied: numpy>=1.21.0 in /usr/local/lib/python3.10/dist-packages (from pandas->schemadisplay-magic) (1.25.2)\n",
            "Requirement already satisfied: pyparsing>=2.1.4 in /usr/local/lib/python3.10/dist-packages (from pydot->schemadisplay-magic) (3.1.1)\n",
            "Requirement already satisfied: typing-extensions>=4.6.0 in /usr/local/lib/python3.10/dist-packages (from sqlalchemy->schemadisplay-magic) (4.9.0)\n",
            "Requirement already satisfied: greenlet!=0.4.17 in /usr/local/lib/python3.10/dist-packages (from sqlalchemy->schemadisplay-magic) (3.0.3)\n",
            "Requirement already satisfied: six>=1.5 in /usr/local/lib/python3.10/dist-packages (from python-dateutil>=2.8.1->pandas->schemadisplay-magic) (1.16.0)\n",
            "Installing collected packages: schemadisplay-magic\n",
            "Successfully installed schemadisplay-magic-0.0.8\n"
          ]
        }
      ],
      "source": [
        "%pip install schemadisplay-magic"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 24,
      "id": "a554bbe2",
      "metadata": {
        "id": "a554bbe2"
      },
      "outputs": [],
      "source": [
        "%load_ext schemadisplay_magic"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 28,
      "id": "a96f818f",
      "metadata": {
        "id": "a96f818f",
        "outputId": "90d88dd3-eaab-43bc-de1a-8eeab620cb1a",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 144
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "image/svg+xml": "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"no\"?>\n<!DOCTYPE svg PUBLIC \"-//W3C//DTD SVG 1.1//EN\"\n \"http://www.w3.org/Graphics/SVG/1.1/DTD/svg11.dtd\">\n<!-- Generated by graphviz version 2.43.0 (0)\n -->\n<!-- Title: G Pages: 1 -->\n<svg width=\"279pt\" height=\"92pt\"\n viewBox=\"0.00 0.00 279.00 92.00\" xmlns=\"http://www.w3.org/2000/svg\" xmlns:xlink=\"http://www.w3.org/1999/xlink\">\n<g id=\"graph0\" class=\"graph\" transform=\"scale(1 1) rotate(0) translate(4 88)\">\n<title>G</title>\n<polygon fill=\"white\" stroke=\"transparent\" points=\"-4,4 -4,-88 275,-88 275,4 -4,4\"/>\n<!-- houses -->\n<g id=\"node1\" class=\"node\">\n<title>houses</title>\n<text text-anchor=\"start\" x=\"205\" y=\"-53.4\" font-family=\"Bitstream-Vera Sans\" font-size=\"7.00\">houses</text>\n<polygon fill=\"none\" stroke=\"black\" points=\"176,-47 176,-49 262,-49 262,-47 176,-47\"/>\n<text text-anchor=\"start\" x=\"178\" y=\"-39.4\" font-family=\"Bitstream-Vera Sans\" font-size=\"7.00\">&#45; id(PK) : INTEGER</text>\n<text text-anchor=\"start\" x=\"178\" y=\"-27.4\" font-family=\"Bitstream-Vera Sans\" font-size=\"7.00\">&#45; name : TEXT</text>\n<polygon fill=\"none\" stroke=\"black\" points=\"175,-22 175,-62 263,-62 263,-22 175,-22\"/>\n</g>\n<!-- students -->\n<g id=\"node2\" class=\"node\">\n<title>students</title>\n<text text-anchor=\"start\" x=\"47.5\" y=\"-71.4\" font-family=\"Bitstream-Vera Sans\" font-size=\"7.00\">students</text>\n<polygon fill=\"none\" stroke=\"black\" points=\"9.5,-65 9.5,-67 122.5,-67 122.5,-65 9.5,-65\"/>\n<text text-anchor=\"start\" x=\"11.5\" y=\"-57.4\" font-family=\"Bitstream-Vera Sans\" font-size=\"7.00\">&#45; id(PK) : INTEGER</text>\n<text text-anchor=\"start\" x=\"11.5\" y=\"-45.4\" font-family=\"Bitstream-Vera Sans\" font-size=\"7.00\">&#45; name : TEXT</text>\n<text text-anchor=\"start\" x=\"11.5\" y=\"-33.4\" font-family=\"Bitstream-Vera Sans\" font-size=\"7.00\">&#45; age : INTEGER</text>\n<text text-anchor=\"start\" x=\"11.5\" y=\"-21.4\" font-family=\"Bitstream-Vera Sans\" font-size=\"7.00\">&#45; house_id(FK) : INTEGER</text>\n<text text-anchor=\"start\" x=\"11.5\" y=\"-9.4\" font-family=\"Bitstream-Vera Sans\" font-size=\"7.00\">&#45; gender : TEXT</text>\n<polygon fill=\"none\" stroke=\"black\" points=\"8,-4 8,-80 123,-80 123,-4 8,-4\"/>\n</g>\n<!-- students&#45;&gt;houses -->\n<g id=\"edge1\" class=\"edge\">\n<title>students&#45;&gt;houses</title>\n<path fill=\"none\" stroke=\"black\" d=\"M141.29,-42C147.1,-42 152.91,-42 158.59,-42\"/>\n<polygon fill=\"black\" stroke=\"black\" points=\"141.19,-42 131.19,-37.5 136.19,-42 131.19,-42 131.19,-42 131.19,-42 136.19,-42 131.19,-46.5 141.19,-42 141.19,-42\"/>\n<ellipse fill=\"none\" stroke=\"black\" cx=\"162.8\" cy=\"-42\" rx=\"4\" ry=\"4\"/>\n<text text-anchor=\"middle\" x=\"157.3\" y=\"-44.4\" font-family=\"Bitstream-Vera Sans\" font-size=\"7.00\">+ id</text>\n<text text-anchor=\"middle\" x=\"154.19\" y=\"-36.4\" font-family=\"Bitstream-Vera Sans\" font-size=\"7.00\">+ house_id</text>\n</g>\n</g>\n</svg>\n",
            "text/plain": [
              "<graphviz.sources.Source at 0x7a4dda779c90>"
            ]
          },
          "metadata": {},
          "execution_count": 28
        }
      ],
      "source": [
        "%schema --connection_string sqlite:///datasets/sqlite-scratch.db"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "f815668a",
      "metadata": {
        "id": "f815668a"
      },
      "source": [
        "Back to Theory for ALTER TABLE"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "cc88363d",
      "metadata": {
        "id": "cc88363d"
      },
      "source": [
        "## ALTER Table"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "075ef3fe",
      "metadata": {
        "id": "075ef3fe"
      },
      "source": [
        "*Exercise*:\n",
        "- Add a column gender in hogwarts_students table\n",
        "- Rename hogwarts_houses in houses\n",
        "- Rename hogwarts_students in students"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 26,
      "id": "65e28ff0",
      "metadata": {
        "id": "65e28ff0",
        "outputId": "698e015d-3a00-47c4-8396-4f8e61f1309c",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///datasets/sqlite-scratch.db\n",
            "Done.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {},
          "execution_count": 26
        }
      ],
      "source": [
        "%%sql\n",
        "ALTER TABLE hogwarts_students ADD column gender TEXT"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 27,
      "id": "deca495f",
      "metadata": {
        "id": "deca495f",
        "outputId": "7c033997-38b6-4411-eade-e84f10ef7011",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///datasets/sqlite-scratch.db\n",
            "Done.\n",
            "Done.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {},
          "execution_count": 27
        }
      ],
      "source": [
        "%%sql\n",
        "ALTER TABLE hogwarts_houses RENAME to houses;\n",
        "ALTER TABLE hogwarts_students RENAME to students;"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "81ef203c",
      "metadata": {
        "id": "81ef203c"
      },
      "source": [
        "Back to Theory for DML"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "c9148d33",
      "metadata": {
        "id": "c9148d33"
      },
      "source": [
        "## Insert into a table (specifying columns)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "7af99bd8",
      "metadata": {
        "id": "7af99bd8"
      },
      "source": [
        "*Exercise*:\n",
        "- Insert Gryffindor, Slytherin  in the houses table\n",
        "\n",
        "nb\n",
        "Since id is primary key, in sqlite it's automatically created with rowid, so we don't need to specify in the column list"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 29,
      "id": "b4a05be5",
      "metadata": {
        "id": "b4a05be5",
        "outputId": "68bfc6b3-5af9-4c68-f614-6944ab657a19",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///datasets/sqlite-scratch.db\n",
            "2 rows affected.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {},
          "execution_count": 29
        }
      ],
      "source": [
        "%%sql\n",
        "INSERT into houses(name)\n",
        "VALUES\n",
        "('Gryffindor'),\n",
        "('Slytherin')"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "2478832e",
      "metadata": {
        "id": "2478832e"
      },
      "source": [
        "Check if rows are inserted"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 30,
      "id": "d1d3ef9a",
      "metadata": {
        "id": "d1d3ef9a",
        "outputId": "b8a8f360-f146-4299-bae4-c6aa28ff1dba",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 117
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///datasets/sqlite-scratch.db\n",
            "Done.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[(1, 'Gryffindor'), (2, 'Slytherin')]"
            ],
            "text/html": [
              "<table>\n",
              "    <thead>\n",
              "        <tr>\n",
              "            <th>id</th>\n",
              "            <th>name</th>\n",
              "        </tr>\n",
              "    </thead>\n",
              "    <tbody>\n",
              "        <tr>\n",
              "            <td>1</td>\n",
              "            <td>Gryffindor</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2</td>\n",
              "            <td>Slytherin</td>\n",
              "        </tr>\n",
              "    </tbody>\n",
              "</table>"
            ]
          },
          "metadata": {},
          "execution_count": 30
        }
      ],
      "source": [
        "%%sql\n",
        "select * from houses"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "2b0fb6fd",
      "metadata": {
        "id": "2b0fb6fd"
      },
      "source": [
        "## CREATE Table Default and Unique\n",
        "- As column constraint it's possible to set a default value for the column using DEFAULT keywork\n",
        "- This setting allows to insert into a table without specifying the columns, if not present a NULL values is set, if the column is defined as NOT NULL, an error arise\n",
        "- A table constraint can also check if multiple columns are UNIQUE"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "c1a221a6",
      "metadata": {
        "id": "c1a221a6"
      },
      "source": [
        "*Exercise*:\n",
        "Albus Silente needs a table to check that all Hogwarts have perfomed the OWL exams.\n",
        "- Create a single table having\n",
        "    - student name: text not null (eg. \"Harry Potter\")\n",
        "    - class name: text not null (eg. \"Defence Against the Dark Arts\")\n",
        "    - grade: integer default 0\n",
        "- Add a table constraint to check UNIQUE on student name, class name"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 31,
      "id": "f2e9e660",
      "metadata": {
        "id": "f2e9e660",
        "outputId": "3ab6eb60-ea0d-4bfc-8f8a-436761668592",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///datasets/sqlite-scratch.db\n",
            "Done.\n",
            "Done.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {},
          "execution_count": 31
        }
      ],
      "source": [
        "%%sql\n",
        "DROP TABLE IF EXISTS owl_records;\n",
        "CREATE TABLE IF NOT EXISTS owl_records(\n",
        "student TEXT NOT NULL,\n",
        "class   TEXT NOT NULL,\n",
        "grade  NUMBER DEFAULT 0,\n",
        "UNIQUE (student,class)\n",
        ")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 34,
      "id": "1735229e",
      "metadata": {
        "id": "1735229e",
        "outputId": "0e304f10-1c5f-4e51-978e-a9bcdea8237e",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///datasets/sqlite-scratch.db\n",
            "(sqlite3.IntegrityError) UNIQUE constraint failed: owl_records.student, owl_records.class\n",
            "[SQL: insert into owl_records(student, class)\n",
            "values\n",
            "('Harry Potter', 'Defence Against the Dark Arts')]\n",
            "(Background on this error at: https://sqlalche.me/e/20/gkpj)\n"
          ]
        }
      ],
      "source": [
        "%%sql\n",
        "insert into owl_records(student, class)\n",
        "values\n",
        "('Harry Potter', 'Defence Against the Dark Arts')"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "f4838484",
      "metadata": {
        "id": "f4838484"
      },
      "source": [
        "Check Records"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 33,
      "id": "89d549d0",
      "metadata": {
        "id": "89d549d0",
        "outputId": "6b18cc7b-4265-41d7-dc5d-ee40a190f473",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 96
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///datasets/sqlite-scratch.db\n",
            "Done.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[('Harry Potter', 'Defence Against the Dark Arts', 0)]"
            ],
            "text/html": [
              "<table>\n",
              "    <thead>\n",
              "        <tr>\n",
              "            <th>student</th>\n",
              "            <th>class</th>\n",
              "            <th>grade</th>\n",
              "        </tr>\n",
              "    </thead>\n",
              "    <tbody>\n",
              "        <tr>\n",
              "            <td>Harry Potter</td>\n",
              "            <td>Defence Against the Dark Arts</td>\n",
              "            <td>0</td>\n",
              "        </tr>\n",
              "    </tbody>\n",
              "</table>"
            ]
          },
          "metadata": {},
          "execution_count": 33
        }
      ],
      "source": [
        "%%sql\n",
        "select * from owl_records"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "19e0f6e9",
      "metadata": {
        "id": "19e0f6e9"
      },
      "source": [
        "Run again the insert query to check unique constraint\n"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "36fcc475",
      "metadata": {
        "id": "36fcc475"
      },
      "source": [
        "## INSERT INTO ... SELECT"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "de174952",
      "metadata": {
        "id": "de174952"
      },
      "source": [
        "*Exercise*:\n",
        "Prof. Mcgonagal needs to create a list of students,\n",
        "- Insert into the students using select into\n",
        "- Bonus add a where clause in the select to avoid repetitions"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 35,
      "id": "4693b2a2",
      "metadata": {
        "id": "4693b2a2",
        "outputId": "fdfe9d16-56a4-433a-b2d8-8351df80d09a",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///datasets/sqlite-scratch.db\n",
            "1 rows affected.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {},
          "execution_count": 35
        }
      ],
      "source": [
        "%%sql\n",
        "insert into students(name) select distinct student from owl_records"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 36,
      "id": "e8f8b944",
      "metadata": {
        "id": "e8f8b944",
        "outputId": "38ec7d31-5e7b-4eeb-ac38-5894f1f55bcb",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 96
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///datasets/sqlite-scratch.db\n",
            "Done.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[(1, 'Harry Potter', None, None, None)]"
            ],
            "text/html": [
              "<table>\n",
              "    <thead>\n",
              "        <tr>\n",
              "            <th>id</th>\n",
              "            <th>name</th>\n",
              "            <th>age</th>\n",
              "            <th>house_id</th>\n",
              "            <th>gender</th>\n",
              "        </tr>\n",
              "    </thead>\n",
              "    <tbody>\n",
              "        <tr>\n",
              "            <td>1</td>\n",
              "            <td>Harry Potter</td>\n",
              "            <td>None</td>\n",
              "            <td>None</td>\n",
              "            <td>None</td>\n",
              "        </tr>\n",
              "    </tbody>\n",
              "</table>"
            ]
          },
          "metadata": {},
          "execution_count": 36
        }
      ],
      "source": [
        "%%sql\n",
        "select * from students"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 37,
      "id": "47f3e167",
      "metadata": {
        "id": "47f3e167",
        "outputId": "bf02e617-5f34-440a-bd30-1a1ff9d43b05",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///datasets/sqlite-scratch.db\n",
            "1 rows affected.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {},
          "execution_count": 37
        }
      ],
      "source": [
        "%%sql\n",
        "insert into owl_records(student, class)\n",
        "values\n",
        "('Hermione Granger', 'Defence Against the Dark Arts')"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 39,
      "id": "fcedfca3",
      "metadata": {
        "id": "fcedfca3",
        "outputId": "5982693c-27ab-4aeb-d133-8ba22eb4733f",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///datasets/sqlite-scratch.db\n",
            "0 rows affected.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {},
          "execution_count": 39
        }
      ],
      "source": [
        "%%sql\n",
        "insert into students(name)\n",
        "select distinct student from owl_records\n",
        "where student not in (select name from students);\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 44,
      "id": "46ddf89f",
      "metadata": {
        "id": "46ddf89f",
        "outputId": "bd32c556-5e88-4b63-84fb-bb732254052f",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 159
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///datasets/sqlite-scratch.db\n",
            "Done.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[(1, 'Harry Potter', None, None, None),\n",
              " (2, 'Hermione Granger', None, None, None),\n",
              " (3, 'Fred', None, None, None),\n",
              " (4, 'George', None, None, None)]"
            ],
            "text/html": [
              "<table>\n",
              "    <thead>\n",
              "        <tr>\n",
              "            <th>id</th>\n",
              "            <th>name</th>\n",
              "            <th>age</th>\n",
              "            <th>house_id</th>\n",
              "            <th>gender</th>\n",
              "        </tr>\n",
              "    </thead>\n",
              "    <tbody>\n",
              "        <tr>\n",
              "            <td>1</td>\n",
              "            <td>Harry Potter</td>\n",
              "            <td>None</td>\n",
              "            <td>None</td>\n",
              "            <td>None</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2</td>\n",
              "            <td>Hermione Granger</td>\n",
              "            <td>None</td>\n",
              "            <td>None</td>\n",
              "            <td>None</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>3</td>\n",
              "            <td>Fred</td>\n",
              "            <td>None</td>\n",
              "            <td>None</td>\n",
              "            <td>None</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>4</td>\n",
              "            <td>George</td>\n",
              "            <td>None</td>\n",
              "            <td>None</td>\n",
              "            <td>None</td>\n",
              "        </tr>\n",
              "    </tbody>\n",
              "</table>"
            ]
          },
          "metadata": {},
          "execution_count": 44
        }
      ],
      "source": [
        "%%sql\n",
        "select * from students\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 43,
      "id": "37e123d5",
      "metadata": {
        "id": "37e123d5",
        "outputId": "c2e89593-9d2c-43c3-8a45-c53680b04bda",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 117
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///datasets/sqlite-scratch.db\n",
            "Done.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[('Harry Potter', 'Defence Against the Dark Arts', 0),\n",
              " ('Hermione Granger', 'Defence Against the Dark Arts', 0)]"
            ],
            "text/html": [
              "<table>\n",
              "    <thead>\n",
              "        <tr>\n",
              "            <th>student</th>\n",
              "            <th>class</th>\n",
              "            <th>grade</th>\n",
              "        </tr>\n",
              "    </thead>\n",
              "    <tbody>\n",
              "        <tr>\n",
              "            <td>Harry Potter</td>\n",
              "            <td>Defence Against the Dark Arts</td>\n",
              "            <td>0</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>Hermione Granger</td>\n",
              "            <td>Defence Against the Dark Arts</td>\n",
              "            <td>0</td>\n",
              "        </tr>\n",
              "    </tbody>\n",
              "</table>"
            ]
          },
          "metadata": {},
          "execution_count": 43
        }
      ],
      "source": [
        "%%sql\n",
        "select * from owl_records\n"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "102f8e16",
      "metadata": {
        "id": "102f8e16"
      },
      "source": [
        "## DELETE"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "4825bac9",
      "metadata": {
        "id": "4825bac9"
      },
      "source": [
        "*Exercise*:\n",
        "Fred and George want to leave the school to open a magic chop\n",
        "- Insert and delete them into the students table"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 42,
      "id": "57100021",
      "metadata": {
        "id": "57100021",
        "outputId": "f4815603-1576-482f-d23c-1bf37b85e94d",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///datasets/sqlite-scratch.db\n",
            "2 rows affected.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {},
          "execution_count": 42
        }
      ],
      "source": [
        "%%sql\n",
        "insert into students(name) values ('Fred'),('George')\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 45,
      "id": "bdb1aa0d",
      "metadata": {
        "id": "bdb1aa0d",
        "outputId": "6c921124-5e1e-4c23-ecaf-ff30729b563d",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///datasets/sqlite-scratch.db\n",
            "2 rows affected.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {},
          "execution_count": 45
        }
      ],
      "source": [
        "%%sql\n",
        "delete from students where name in ('Fred','George')"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "9dac94df",
      "metadata": {
        "id": "9dac94df"
      },
      "source": [
        "*Exercise*:\n",
        "Peeves does a prank:\n",
        "- Delete all records in owl_records table"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 46,
      "id": "85159630",
      "metadata": {
        "id": "85159630",
        "outputId": "0a69a643-38d5-4880-f5b6-5a7671179c4d",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///datasets/sqlite-scratch.db\n",
            "2 rows affected.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {},
          "execution_count": 46
        }
      ],
      "source": [
        "%%sql\n",
        "delete from owl_records"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "d4d80f30",
      "metadata": {
        "id": "d4d80f30"
      },
      "source": [
        "## UPDATE"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "c29ed24f",
      "metadata": {
        "id": "c29ed24f"
      },
      "source": [
        "*Exercise*: In the Sorting Hat ceremony, Harry and Hermione are assigned to Gryffindor house:\n",
        "- update students table accordingly\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 47,
      "id": "7cfd51d7",
      "metadata": {
        "id": "7cfd51d7",
        "outputId": "20e78c92-e710-4474-f596-c3ea4b034091",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 117
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///datasets/sqlite-scratch.db\n",
            "Done.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[(1, 'Gryffindor'), (2, 'Slytherin')]"
            ],
            "text/html": [
              "<table>\n",
              "    <thead>\n",
              "        <tr>\n",
              "            <th>id</th>\n",
              "            <th>name</th>\n",
              "        </tr>\n",
              "    </thead>\n",
              "    <tbody>\n",
              "        <tr>\n",
              "            <td>1</td>\n",
              "            <td>Gryffindor</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2</td>\n",
              "            <td>Slytherin</td>\n",
              "        </tr>\n",
              "    </tbody>\n",
              "</table>"
            ]
          },
          "metadata": {},
          "execution_count": 47
        }
      ],
      "source": [
        "%%sql\n",
        "select * from houses"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 48,
      "id": "acdb7e0c",
      "metadata": {
        "id": "acdb7e0c",
        "outputId": "56e1b834-4fc2-4d2d-dc52-a2bd44316a4e",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///datasets/sqlite-scratch.db\n",
            "2 rows affected.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {},
          "execution_count": 48
        }
      ],
      "source": [
        "%%sql\n",
        "update students set house_id=1 where name in ('Harry Potter','Hermione Granger')"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "%%sql\n",
        "select\n",
        "s.name student_name,\n",
        "h.name house_name\n",
        "from students s\n",
        "join houses h on h.id=s.house_id"
      ],
      "metadata": {
        "id": "EaFxdvgErUDR",
        "outputId": "76dfacce-6979-4431-c6e7-1fb32e7e7408",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 117
        }
      },
      "id": "EaFxdvgErUDR",
      "execution_count": 52,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///datasets/sqlite-scratch.db\n",
            "Done.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[('Harry Potter', 'Gryffindor'), ('Hermione Granger', 'Gryffindor')]"
            ],
            "text/html": [
              "<table>\n",
              "    <thead>\n",
              "        <tr>\n",
              "            <th>student_name</th>\n",
              "            <th>house_name</th>\n",
              "        </tr>\n",
              "    </thead>\n",
              "    <tbody>\n",
              "        <tr>\n",
              "            <td>Harry Potter</td>\n",
              "            <td>Gryffindor</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>Hermione Granger</td>\n",
              "            <td>Gryffindor</td>\n",
              "        </tr>\n",
              "    </tbody>\n",
              "</table>"
            ]
          },
          "metadata": {},
          "execution_count": 52
        }
      ]
    },
    {
      "cell_type": "markdown",
      "id": "2e4a37f6",
      "metadata": {
        "id": "2e4a37f6"
      },
      "source": [
        "## Close connection"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 53,
      "id": "iuNaD-1EoVuR",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "iuNaD-1EoVuR",
        "outputId": "8d02d680-a5c8-4138-9fec-e73340ab8dbc"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "{'sqlite:///datasets/sqlite-scratch.db': <sql.connection.Connection at 0x7a4ddad9e170>}"
            ]
          },
          "metadata": {},
          "execution_count": 53
        }
      ],
      "source": [
        "%sql -l"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "xuRawWL5o_MQ",
      "metadata": {
        "id": "xuRawWL5o_MQ"
      },
      "outputs": [],
      "source": [
        "%sql -x sqlite:///datasets/sqlite-scratch.db"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "UtsmV4-HpDjl",
      "metadata": {
        "id": "UtsmV4-HpDjl"
      },
      "outputs": [],
      "source": []
    }
  ],
  "metadata": {
    "celltoolbar": "Slideshow",
    "colab": {
      "provenance": []
    },
    "kernelspec": {
      "display_name": "Python 3 (ipykernel)",
      "language": "python",
      "name": "python3"
    },
    "language_info": {
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "file_extension": ".py",
      "mimetype": "text/x-python",
      "name": "python",
      "nbconvert_exporter": "python",
      "pygments_lexer": "ipython3",
      "version": "3.9.15"
    },
    "rise": {
      "autolaunch": true,
      "enable_chalkboard": "true",
      "footer": "<div class=\"sds-footer\"> *** Social Data Science (SDS) - Notions of Database Management Systems ***</div>",
      "header": "<div class=\"sds-header\"><div class=\"sds-header-image\"></div></div>",
      "scroll": true,
      "theme": "white"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 5
}