{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "443bb4b8-10b4-4875-b727-e219fc6ea6f3",
   "metadata": {},
   "outputs": [],
   "source": [
    "from data201 import db_connection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e8ae4f44-7c6c-4adc-ab8d-992892f34f5f",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = db_connection(config_file = 'school.ini')\n",
    "cursor = conn.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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": null,
   "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": null,
   "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": null,
   "id": "e6108245-e781-4a2c-b9c6-47bb558aa993",
   "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\n",
    "        JOIN takes\n",
    "          ON takes.student_id = student.id\n",
    "        JOIN class\n",
    "          ON class.code = takes.class_code\n",
    "        WHERE student.id = @sid;\n",
    "    END\n",
    "    \"\"\"\n",
    "              )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "848a9749-487b-4c3e-82b5-074aea98f718",
   "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 s.first, s.last, c.code, c.subject\n",
    "        FROM student s\n",
    "        JOIN takes t\n",
    "          ON t.student_id = s.id\n",
    "        JOIN class c\n",
    "          ON c.code = t.class_code\n",
    "        WHERE c.code IN (\n",
    "            SELECT c.code \n",
    "            FROM class c\n",
    "            JOIN takes t\n",
    "              ON t.class_code = c.code\n",
    "            JOIN student s\n",
    "              ON s.id = t.student_id\n",
    "            WHERE s.id = @target_id\n",
    "        )\n",
    "        AND s.id != @target_id;\n",
    "    END\n",
    "    \"\"\"\n",
    "              )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ae032683-fb89-4190-8a71-ee364c5d4ea2",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1f5a57da-c127-4a53-ac2d-2d78a161ca8a",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.12.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}