{ "cells": [ { "cell_type": "code", "execution_count": null, "id": "b5a3c360-ddd6-4cdf-b177-a670253fb7f3", "metadata": {}, "outputs": [], "source": [ "from DATA225utils import make_connection, dataframe_query" ] }, { "cell_type": "code", "execution_count": null, "id": "cb1e52fa-09c5-4e13-83e2-e33849f81aae", "metadata": {}, "outputs": [], "source": [ "conn = make_connection(config_file = 'school.ini')\n", "cursor = conn.cursor()" ] }, { "cell_type": "markdown", "id": "72f8e3ef-eb7c-4718-bc42-4e1c3d1891ba", "metadata": {}, "source": [ "## Nested `SELECT`" ] }, { "cell_type": "markdown", "id": "721c69f9-d1fa-4879-bdc1-fdc753813a90", "metadata": {}, "source": [ "#### Who are the classmates of Tim Novak?" ] }, { "cell_type": "code", "execution_count": null, "id": "fe831143-a36f-41a1-bcff-c4e2a78141c8", "metadata": {}, "outputs": [], "source": [ "_, df = dataframe_query(conn, \"\"\"\n", " SELECT s.first, s.last, c.code, c.subject\n", " FROM student s, takes t, class c\n", " WHERE c.code IN (\n", " SELECT c.code \n", " FROM student s, class c, takes t\n", " WHERE s.first = \"Tim\"\n", " AND s.last = \"Novak\"\n", " AND s.id = t.student_id\n", " AND c.code = t.class_code\n", " )\n", " AND (c.code = t.class_code)\n", " AND (s.id = student_id)\n", " AND NOT ( (s.first = \"Tim\")\n", " AND (s.last = \"Novak\"))\n", " \"\"\"\n", " )\n", "\n", "df" ] }, { "cell_type": "markdown", "id": "b79978af-27cb-41f4-a313-b388803b798d", "metadata": {}, "source": [ "## Use of a view instead\n", "\n", "#### Create a view from the nested `SELECT`. " ] }, { "cell_type": "code", "execution_count": null, "id": "1047b666-2fc0-44f0-a8af-6d4a147ac67f", "metadata": {}, "outputs": [], "source": [ "cursor.execute('DROP VIEW IF EXISTS class_codes_of_novak')\n", "\n", "cursor.execute( \"\"\"\n", " CREATE VIEW class_codes_of_novak AS\n", " SELECT c.code \n", " FROM student s, class c, takes t\n", " WHERE s.first = \"Tim\"\n", " AND s.last = \"Novak\"\n", " AND s.id = t.student_id\n", " AND c.code = t.class_code\n", " \"\"\"\n", " )" ] }, { "cell_type": "code", "execution_count": null, "id": "45d8f283-3458-4524-bebd-29998ae26629", "metadata": {}, "outputs": [], "source": [ "_, df = dataframe_query(conn, 'SELECT * FROM class_codes_of_novak')\n", "\n", "df" ] }, { "cell_type": "code", "execution_count": null, "id": "3e46c2a9-3d08-4302-89e0-8aeba1f7f5bc", "metadata": {}, "outputs": [], "source": [ "_, df = dataframe_query(conn, \"\"\"\n", " SELECT s.first, s.last, c.code, c.subject\n", " FROM student s, takes t, class c\n", " WHERE c.code IN (\n", " SELECT * FROM class_codes_of_novak\n", " )\n", " AND (c.code = t.class_code)\n", " AND (s.id = student_id)\n", " AND NOT ( (s.first = \"Tim\")\n", " AND (s.last = \"Novak\"))\n", " \"\"\"\n", " )\n", "\n", "df" ] }, { "cell_type": "markdown", "id": "7c61ebb9-3a58-4b65-b16a-6b2cc61fbd32", "metadata": {}, "source": [ "## A better use of the view" ] }, { "cell_type": "code", "execution_count": null, "id": "e83fe3e7-01a9-4979-8750-ad56fb0635e3", "metadata": {}, "outputs": [], "source": [ "_, df = dataframe_query(conn, \"\"\"\n", " SELECT s.first, s.last, c.code, c.subject\n", " FROM student s, takes t, class c, \n", " class_codes_of_novak ccn\n", " WHERE (c.code = ccn.code)\n", " AND (c.code = t.class_code)\n", " AND (s.id = student_id)\n", " AND NOT ( (s.first = \"Tim\")\n", " AND (s.last = \"Novak\"))\n", " \"\"\"\n", " )\n", "\n", "df" ] }, { "cell_type": "markdown", "id": "6956dc99-a167-4517-b5f0-e8a5d38745db", "metadata": {}, "source": [ "## A classmates function\n", "\n", "#### In this case, we don't bother with creating a view since we are inserting the student's first and last names into the query." ] }, { "cell_type": "code", "execution_count": null, "id": "e62c4952-c1de-4d7f-9240-bd4978bf87ec", "metadata": {}, "outputs": [], "source": [ "def classmates_of(first, last):\n", " _, df = dataframe_query(conn, \n", " \"\"\"\n", " SELECT s.first, s.last, c.code, c.subject\n", " FROM student s, takes t, class c\n", " WHERE c.code IN (\n", " SELECT c.code \n", " FROM student s, class c, takes t\n", " \"\"\"\n", " f'WHERE s.first = \"{first}\"'\n", " f'AND s.last = \"{last}\"'\n", " \"\"\"\n", " AND s.id = t.student_id\n", " AND c.code = t.class_code\n", " )\n", " AND (c.code = t.class_code)\n", " AND (s.id = student_id)\n", " \"\"\"\n", " f'AND NOT ( (s.first = \"{first}\")'\n", " f' AND (s.last = \"{last}\"))'\n", " )\n", " \n", " return df" ] }, { "cell_type": "code", "execution_count": null, "id": "dddd8b74-f6f8-455e-ae20-e9d081236ccd", "metadata": {}, "outputs": [], "source": [ "classmates_of('Tim', 'Novak')" ] }, { "cell_type": "code", "execution_count": null, "id": "11df64bc-e65e-4381-88dc-57b0370fabb8", "metadata": {}, "outputs": [], "source": [ "cursor.execute('SELECT first, last FROM student')\n", "\n", "students = cursor.fetchall()\n", "students" ] }, { "cell_type": "code", "execution_count": null, "id": "d6c4388b-0f9c-4fe4-87f0-c7a2dda03d1f", "metadata": {}, "outputs": [], "source": [ "for first, last in students:\n", " print()\n", " print(f\"Classmates of {first + ' ' + last}:\")\n", " \n", " display(classmates_of(first, last))" ] }, { "cell_type": "code", "execution_count": null, "id": "23fc777e-1e5d-4d4b-9787-99cef56ce553", "metadata": {}, "outputs": [], "source": [ "cursor.close()\n", "conn.close()" ] }, { "cell_type": "code", "execution_count": null, "id": "840a4f11-4c7f-4e61-bc95-872f4ab26b72", "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 }