{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "443bb4b8-10b4-4875-b727-e219fc6ea6f3",
   "metadata": {},
   "outputs": [],
   "source": [
    "from DATA225utils import make_connection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "e8ae4f44-7c6c-4adc-ab8d-992892f34f5f",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = make_connection(config_file = 'school.ini')\n",
    "cursor = conn.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "98edb864-d692-44f4-8f59-bd02dbc88652",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute('DROP PROCEDURE IF EXISTS all_students')\n",
    "cursor.execute('DROP PROCEDURE IF EXISTS id_of')\n",
    "cursor.execute('DROP PROCEDURE IF EXISTS class_codes_of')\n",
    "cursor.execute('DROP PROCEDURE IF EXISTS classmates_of')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "bf9387b8-4b51-4095-babc-c40b91038d41",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "cursor.execute( \n",
    "    \"\"\"\n",
    "    CREATE PROCEDURE all_students()\n",
    "    BEGIN\n",
    "        SELECT *\n",
    "        FROM student;\n",
    "    END\n",
    "    \"\"\"\n",
    "              )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "2fc704bf-5292-42c3-83d5-d06ea86a38e9",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute( \n",
    "    \"\"\"\n",
    "    CREATE PROCEDURE id_of(IN first_name VARCHAR(32), \n",
    "                           IN last_name VARCHAR(32),\n",
    "                           OUT sid VARCHAR(5))\n",
    "    BEGIN\n",
    "        SELECT id INTO sid\n",
    "        FROM student\n",
    "        WHERE first = first_name\n",
    "        AND   last  = last_name;\n",
    "    END\n",
    "    \"\"\"\n",
    "              )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "06da2df4-0033-4e01-9924-83e1543eda6c",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute( \n",
    "    \"\"\"\n",
    "    CREATE PROCEDURE class_codes_of(IN first_name VARCHAR(32), \n",
    "                                    IN last_name VARCHAR(32))\n",
    "    BEGIN\n",
    "        CALL id_of(first_name, last_name, @sid);\n",
    "\n",
    "        SELECT code\n",
    "        FROM student, class, takes\n",
    "        WHERE id = @sid\n",
    "        AND   student.id = takes.student_id\n",
    "        AND   class.code = takes.class_code;\n",
    "    END\n",
    "    \"\"\"\n",
    "              )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "7ac6f1b6-5f9c-4ab6-af2a-a10c6ea872ec",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute( \n",
    "    \"\"\"\n",
    "    CREATE PROCEDURE classmates_of(IN target_first_name VARCHAR(32), \n",
    "                                   IN target_last_name VARCHAR(32))\n",
    "    BEGIN\n",
    "        CALL id_of(target_first_name, target_last_name, @target_id);\n",
    "\n",
    "        SELECT DISTINCT student.first, student.last, \n",
    "                        class.code, class.subject\n",
    "        FROM student, takes, class\n",
    "        WHERE class.code IN (\n",
    "            SELECT class.code \n",
    "            FROM student, class, takes\n",
    "            WHERE (student.id = @target_id)\n",
    "            AND   (student.id = takes.student_id)\n",
    "            AND   (takes.class_code = code)\n",
    "        )\n",
    "        AND (class.code = takes.class_code)\n",
    "        AND (student.id = takes.student_id)\n",
    "        AND (student.id != @target_id);\n",
    "    END\n",
    "    \"\"\"\n",
    "              )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "ae032683-fb89-4190-8a71-ee364c5d4ea2",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.close()\n",
    "conn.close()"
   ]
  }
 ],
 "metadata": {
  "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.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}