lab1.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 752,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The sql extension is already loaded. To reload it, use:\n",
      "  %reload_ext sql\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'Connected: @lab1.db3'"
      ]
     },
     "execution_count": 752,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%load_ext sql\n",
    "%sql sqlite:///lab1.db3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 753,
   "metadata": {},
   "outputs": [],
   "source": [
    "## CREATE TABLE MYGRAPH TO STORE DIRECTED GRAPH"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 754,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 754,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql sqlite://\n",
    "drop table if exists MyGraph;\n",
    "CREATE TABLE MyGraph(\n",
    "    ID VARCHAR(10), \n",
    "    Source int NOT NULL, \n",
    "    Destination int NOT NULL\n",
    ");"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 755,
   "metadata": {},
   "outputs": [],
   "source": [
    "## STORE VALUES FROM GRAPH INTO MYGRAPH"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 756,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///lab1.db3\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 756,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "insert into MyGraph values (1, 5, 11);\n",
    "insert into MyGraph values (2, 7, 11);\n",
    "insert into MyGraph values (3, 7, 8);\n",
    "insert into MyGraph values (4, 3, 8);\n",
    "insert into MyGraph values (5, 3, 10);\n",
    "insert into MyGraph values (6, 11, 2);\n",
    "insert into MyGraph values (7, 11, 9);\n",
    "insert into MyGraph values (8, 11, 10);\n",
    "insert into MyGraph values (9, 8, 9);\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 757,
   "metadata": {},
   "outputs": [],
   "source": [
    "## RETURNS ALL SOURCE VERTICES IN MYGRAPH (DISTINCT)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 758,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///lab1.db3\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Source</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>5</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>7</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>11</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>8</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(5,), (7,), (3,), (11,), (8,)]"
      ]
     },
     "execution_count": 758,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT distinct k.Source \n",
    "FROM MyGraph k"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 759,
   "metadata": {},
   "outputs": [],
   "source": [
    "## RETURNS ALL EDGES, SUCH THAT SOURCE ID > DESITINATION ID "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 760,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///lab1.db3\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Source</th>\n",
       "            <th>Destination</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>11</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>11</td>\n",
       "            <td>9</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>11</td>\n",
       "            <td>10</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(11, 2), (11, 9), (11, 10)]"
      ]
     },
     "execution_count": 760,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT k.Source, k.Destination \n",
    "FROM MyGraph k \n",
    "WHERE k.Source > k.Destination"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 761,
   "metadata": {},
   "outputs": [],
   "source": [
    "## ADD A COLUMN OF WEIGHT TO EACH LINK"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 762,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///lab1.db3\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 762,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "ALTER TABLE MyGraph \n",
    "ADD column Weight int;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 763,
   "metadata": {},
   "outputs": [],
   "source": [
    "## GENERATE RANDOM NUMBER WITH SEED"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 764,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///lab1.db3\n",
      "Done.\n",
      "3\n",
      "10\n",
      "2\n",
      "5\n",
      "2\n",
      "8\n",
      "8\n",
      "8\n",
      "7\n",
      "4\n"
     ]
    }
   ],
   "source": [
    "%sql SELECT abs(random()) % (10 - 1) + 1;\n",
    "\n",
    "from random import random\n",
    "from random import seed\n",
    "from random import randint\n",
    "\n",
    "seed(1)\n",
    "\n",
    "for _ in range (10):\n",
    "    value = randint(1, 10)\n",
    "    print(value)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 765,
   "metadata": {},
   "outputs": [],
   "source": [
    "## UPDATE WEIGHT COLUMN TO ASSIGN RANDOM NUMBER TO EACH LINK\n",
    "## USING THE DISPLAYED VALUES FROM THE RANDOM NUMBER GENERATOR ABOVE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 766,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///lab1.db3\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 766,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "UPDATE MyGraph SET Weight = 3 WHERE ID = 1;\n",
    "UPDATE MyGraph SET Weight = 10 WHERE ID = 2;\n",
    "UPDATE MyGraph SET Weight = 2 WHERE ID = 3;\n",
    "UPDATE MyGraph SET Weight = 5 WHERE ID = 4;\n",
    "UPDATE MyGraph SET Weight = 2 WHERE ID = 5;\n",
    "UPDATE MyGraph SET Weight = 8 WHERE ID = 6;\n",
    "UPDATE MyGraph SET Weight = 8 WHERE ID = 7;\n",
    "UPDATE MyGraph SET Weight = 8 WHERE ID = 8;\n",
    "UPDATE MyGraph SET Weight = 7 WHERE ID = 9;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 767,
   "metadata": {},
   "outputs": [],
   "source": [
    "## RETURN DISTINCT VALUES OF WEIGHT"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 768,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///lab1.db3\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Weight</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>10</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>5</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>8</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>7</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(3,), (10,), (2,), (5,), (8,), (7,)]"
      ]
     },
     "execution_count": 768,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "SELECT distinct Weight\n",
    "FROM MyGraph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}