practiceExercise.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "authorship_tag": "ABX9TyOUpBFMK/ueVjlzfoqzH/fp",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/Dan-data-tech/SQLite3/blob/main/practiceExercise.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# **CREATING THE DATABASE**"
      ],
      "metadata": {
        "id": "8lqfkCuLLkP8"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import sqlite3\n",
        "connection = sqlite3.connect('cybersec_incident_response.db')\n",
        "cursor=connection.cursor()"
      ],
      "metadata": {
        "id": "-c-TBLqQK8rb"
      },
      "execution_count": 2,
      "outputs": []
    },
    {
      "cell_type": "code",
      "execution_count": 3,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "collapsed": true,
        "id": "TQBLLNMYGmUl",
        "outputId": "007d3bc6-c08d-49ce-e7d5-50aeba589857"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<sqlite3.Cursor at 0x7bc5368ad940>"
            ]
          },
          "metadata": {},
          "execution_count": 3
        }
      ],
      "source": [
        "cursor.execute('''\n",
        "   CREATE TABLE IF NOT EXISTS incidents(\n",
        "             id INTEGER PRIMARY KEY  AUTOINCREMENT ,\n",
        "             incident_type TEXT,\n",
        "             description TEXT,\n",
        "             reported_date TEXT,\n",
        "             status TEXT\n",
        "   )\n",
        "''')\n",
        "\n",
        "cursor.execute('''\n",
        "   CREATE TABLE IF NOT EXISTS actions(\n",
        "             id INTEGER PRIMARY KEY  AUTOINCREMENT ,\n",
        "             incident_id INTEGER ,\n",
        "             action_description TEXT,\n",
        "             action_date TEXT,\n",
        "             analyst_name TEXT,\n",
        "             FOREIGN KEY (incident_id) REFERENCES incidents(id)\n",
        "   )\n",
        "''')\n",
        "\n",
        "cursor.execute('''\n",
        "   CREATE TABLE IF NOT EXISTS systems(\n",
        "             id INTEGER PRIMARY KEY  AUTOINCREMENT ,\n",
        "             system_name TEXT,\n",
        "             ip_address  TEXT,\n",
        "             status TEXT\n",
        "   )\n",
        "''')\n",
        "\n",
        "\n",
        "incidents_data = [\n",
        "    ('Phishing Attack', 'Employee received a suspicious email', '2024-06-01', 'Open'),\n",
        "    ('Malware Infection', 'System infected by ransomware', '2024-06-02', 'Open'),\n",
        "    ('DDoS Attack', 'Website under DDoS attack', '2024-06-03', 'Resolved')\n",
        "]\n",
        "\n",
        "actions_data = [\n",
        "    ( \"Investigated network breach\", \"2024-06-01\", \"Alice Smith\"),\n",
        "    ( \"Patched security vulnerability\", \"2024-06-02\", \"Bob Johnson\"),\n",
        "    ( \"Reviewed firewall rules\", \"2024-06-03\", \"Carol White\"),\n",
        "    ( \"Conducted system audit\", \"2024-06-04\", \"David Brown\")\n",
        "]\n",
        "\n",
        "systems_data = [\n",
        "    ( \"Server1\", \"192.168.1.1\", \"active\"),\n",
        "    ( \"Server2\", \"192.168.1.2\", \"inactive\"),\n",
        "    ( \"Server3\", \"192.168.1.3\", \"active\"),\n",
        "    ( \"Server4\", \"192.168.1.4\", \"inactive\")\n",
        "]\n",
        "\n",
        "cursor.executemany('INSERT INTO incidents( incident_type, description, reported_date, status ) VALUES ( ?,?,?,?)' , incidents_data)\n",
        "cursor.executemany('INSERT INTO actions( action_description, action_date, analyst_name ) VALUES ( ?,?,?)' , actions_data)\n",
        "cursor.executemany('INSERT INTO systems( system_name, ip_address, status ) VALUES ( ?,?,?)' , systems_data)\n",
        "\n",
        "\n",
        ""
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# **QUERY DATA**"
      ],
      "metadata": {
        "id": "hMlIjyTNKsJN"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "cursor.execute('SELECT * FROM incidents')\n",
        "results = cursor.fetchall()\n",
        "\n",
        "for result in results:\n",
        "    print(result)\n",
        "\n",
        "print('\\n')\n",
        "\n",
        "cursor.execute(\"SELECT  * FROM actions WHERE action_description='Reviewed firewall rules' \")\n",
        "results = cursor.fetchall()\n",
        "\n",
        "for result in results:\n",
        "    print(result)\n",
        "\n",
        "print('\\n')\n",
        "\n",
        "cursor.execute('SELECT * FROM systems')\n",
        "results = cursor.fetchall()\n",
        "\n",
        "for result in results:\n",
        "    print(result)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "EJ5dWqUkKuzz",
        "outputId": "7793d995-73d2-487c-89ab-1db2174cd1ef"
      },
      "execution_count": 4,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "(1, 'Phishing Attack', 'Employee received a suspicious email', '2024-06-01', 'Open')\n",
            "(2, 'Malware Infection', 'System infected by ransomware', '2024-06-02', 'Open')\n",
            "(3, 'DDoS Attack', 'Website under DDoS attack', '2024-06-03', 'Resolved')\n",
            "\n",
            "\n",
            "(3, None, 'Reviewed firewall rules', '2024-06-03', 'Carol White')\n",
            "\n",
            "\n",
            "(1, 'Server1', '192.168.1.1', 'active')\n",
            "(2, 'Server2', '192.168.1.2', 'inactive')\n",
            "(3, 'Server3', '192.168.1.3', 'active')\n",
            "(4, 'Server4', '192.168.1.4', 'inactive')\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# **UPDATE RECORDS**"
      ],
      "metadata": {
        "id": "qQ545l1vKcza"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "cursor.execute(\"UPDATE incidents SET status='Resolved' WHERE description='System infected by ransomware'  \" )\n",
        "cursor.execute(\"UPDATE systems SET status='inactive' WHERE ip_address='192.168.1.3'  \" )\n",
        "\n",
        "\n",
        "cursor.execute(\"SELECT * FROM incidents WHERE description='System infected by ransomware' \")\n",
        "inrecord=cursor.fetchall()\n",
        "print(inrecord)\n",
        "\n",
        "\n",
        "cursor.execute(\"SELECT * FROM systems WHERE ip_address='192.168.1.3'  \")\n",
        "sysrecord=cursor.fetchall()\n",
        "print(sysrecord)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "R-7hYQxnKht9",
        "outputId": "1b96c150-a513-4ca0-8e02-657c386cb21d"
      },
      "execution_count": 5,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "[(2, 'Malware Infection', 'System infected by ransomware', '2024-06-02', 'Resolved')]\n",
            "[(3, 'Server3', '192.168.1.3', 'inactive')]\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# **DELETE RECORDS**"
      ],
      "metadata": {
        "id": "rBln5qRlKHRM"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "cursor.execute(\"DELETE FROM actions WHERE  analyst_name='Bob Johnson' \")\n",
        "cursor.execute(\"DELETE FROM systems WHERE  system_name='Server1' \")\n",
        "\n",
        "cursor.execute('SELECT * FROM actions')\n",
        "results = cursor.fetchall()\n",
        "\n",
        "for result in results:\n",
        "    print(result)\n",
        "print('\\n')\n",
        "cursor.execute('SELECT * FROM systems')\n",
        "results = cursor.fetchall()\n",
        "\n",
        "for result in results:\n",
        "    print(result)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "WUyl-mwwKUFr",
        "outputId": "7847112d-4bdb-4672-ac1b-2ed65045a9f0"
      },
      "execution_count": 6,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "(1, None, 'Investigated network breach', '2024-06-01', 'Alice Smith')\n",
            "(3, None, 'Reviewed firewall rules', '2024-06-03', 'Carol White')\n",
            "(4, None, 'Conducted system audit', '2024-06-04', 'David Brown')\n",
            "\n",
            "\n",
            "(2, 'Server2', '192.168.1.2', 'inactive')\n",
            "(3, 'Server3', '192.168.1.3', 'inactive')\n",
            "(4, 'Server4', '192.168.1.4', 'inactive')\n"
          ]
        }
      ]
    }
  ]
}