{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "534e2c8e-4a16-4eb5-93c0-9fd5cac84509",
   "metadata": {},
   "outputs": [],
   "source": [
    "from DATA225utils import make_connection, dataframe_query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cc176aa9-9580-4866-8054-3812fc22151f",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = make_connection('school.ini')\n",
    "cursor = conn.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e6f2aced-b2ad-452f-a77d-c05c69f35c3c",
   "metadata": {},
   "source": [
    "# Who are the classmates of Tim Novak?\n",
    "### Which classes does Tim Novak take?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "81c7f7db-938b-4623-9e4e-d98ac5abd243",
   "metadata": {},
   "outputs": [],
   "source": [
    "_, df = dataframe_query(conn, \n",
    "    \"\"\"\n",
    "    SELECT code, subject \n",
    "    FROM student, class, takes \n",
    "    WHERE last = 'Novak' \n",
    "    AND first = 'Tim' \n",
    "    AND id = student_id \n",
    "    AND code = class_code    \n",
    "    \"\"\"\n",
    ")\n",
    "\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "62c9bac1-747a-476d-852e-bfd6c1fabd62",
   "metadata": {},
   "source": [
    "### What are his class codes?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ad274987-7187-4f1f-a320-c397220fb1e4",
   "metadata": {},
   "outputs": [],
   "source": [
    "_, df = dataframe_query(conn, \n",
    "    \"\"\"\n",
    "    SELECT code \n",
    "    FROM student, class, takes \n",
    "    WHERE last = 'Novak' \n",
    "    AND first = 'Tim' \n",
    "    AND id = student_id \n",
    "    AND code = class_code    \n",
    "    \"\"\"\n",
    ")\n",
    "\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "06283e7d-087f-4003-90be-29442a4c3f04",
   "metadata": {},
   "source": [
    "### Which classes do the other students take?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1fca9bb2-8175-4553-8ab1-2fe832f2adf3",
   "metadata": {},
   "outputs": [],
   "source": [
    "_, df = dataframe_query(conn, \n",
    "    \"\"\"\n",
    "    SELECT id, first, last, code\n",
    "    FROM student, class, takes\n",
    "    WHERE id = student_id \n",
    "    AND code = class_code \n",
    "    AND last != 'Novak' \n",
    "    AND first != 'Tim'   \n",
    "    ORDER BY last\n",
    "    \"\"\"\n",
    ")\n",
    "\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fa1404b7-eaae-4b9a-b698-ca7b11d1d40f",
   "metadata": {},
   "source": [
    "### Only include the other student's classes that are in the list of Tim Novak's classes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7dac932c-98bb-4f4b-928a-e831006667b6",
   "metadata": {},
   "outputs": [],
   "source": [
    "_, df = dataframe_query(conn,\n",
    "    \"\"\"\n",
    "    SELECT id, first, last, code\n",
    "    FROM student, class, takes\n",
    "    WHERE id = student_id \n",
    "    AND code = class_code \n",
    "    AND last != 'Novak' \n",
    "    AND first != 'Tim' \n",
    "    AND class_code IN (\n",
    "        SELECT code\n",
    "        FROM student, class, takes \n",
    "        WHERE last = 'Novak' \n",
    "        AND first = 'Tim' \n",
    "        AND id = student_id \n",
    "        AND code = class_code\n",
    "    )    \n",
    "    ORDER BY last\n",
    "    \"\"\"\n",
    ")\n",
    "\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cb813b25-0f95-4036-b34e-d8e3fa528e24",
   "metadata": {},
   "source": [
    "### Distinct student ids and names."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "319600b1-ac4f-4e71-b7e6-ab3a48f0eb1c",
   "metadata": {},
   "outputs": [],
   "source": [
    "_, df = dataframe_query(conn, \"\"\"\n",
    "    SELECT distinct id, first, last\n",
    "    FROM student, class, takes\n",
    "    WHERE id = student_id \n",
    "    AND code = class_code \n",
    "    AND last != 'Nova' \n",
    "    AND first != 'Tim' \n",
    "    AND class_code IN (\n",
    "        SELECT code\n",
    "        FROM student, class, takes \n",
    "        WHERE last = 'Novak' \n",
    "        AND first = 'Tim' \n",
    "        AND id = student_id \n",
    "        AND code = class_code\n",
    "    )    \n",
    "    ORDER BY last\n",
    "    \"\"\"\n",
    "                       )\n",
    "\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8ed23ce6-0838-4985-8ea7-fb86dd637e99",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "76693866-e7e1-4a91-a270-920b3b34b171",
   "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.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}