studentcourse-llm.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "source": [
        "# Getting Started (with Google Colab)\n",
        "- In the [Google Colab](https://colab.research.google.com/) menu, go to File -> Open notebook\n",
        "- In the dialogue that appears, click the GitHub tab\n",
        "- Enter this repo as your GitHub URL, i.e., https://github.com/davedgd/studentcourse\n",
        "- Click the search icon (or hit enter)\n",
        "- Select the notebook you want to open to get started (e.g., studentcourse-llm.ipynb)\n",
        "- **Note:** You should also change the runtime type to one with a GPU...\n",
        "    - In the Google Colab menu, go to Runtime -> Change runtime type\n",
        "    - Under 'Hardware accelerator' choose at least a 'T4 GPU' (or better)\n",
        "    - Click the Save button\n",
        "- To run the notebook, in the Google Colab menu, go to Runtime -> Run all\n",
        "- If you see a 'Warning: This notebook was not authored by Google' popup, click the 'Run anyway' button\n",
        "- Wait patiently, as it make take a few minutes to install the packages and run the notebook\n",
        "- **Note:** You may see occasional error messages, particularly when installing/loading pip packages -- you can generally safely ignore these (as long as the notebook runs)"
      ],
      "metadata": {
        "id": "Iyw21tTMmrKf"
      }
    },
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "id": "m4I7XENH_EgK",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "16400148-92d9-4bc0-f774-3c9340a98a73"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "  Preparing metadata (setup.py) ... \u001b[?25l\u001b[?25hdone\n"
          ]
        }
      ],
      "source": [
        "# note: you can generally ignore ERROR messages regarding \"pip's dependency resolver\"\n",
        "!pip install transformers huggingface_hub[hf_transfer] autoawq -U -q --progress-bar off\n",
        "\n",
        "import os\n",
        "os.environ['HF_HUB_ENABLE_HF_TRANSFER'] = '1'"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 2,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 176,
          "referenced_widgets": [
            "d331897cbe0b4c34ab9d34e9387ef4db",
            "c2843f3c972e46c992f3cca451faced8",
            "6ab2e324e1cc427aa4acc17081dd8494",
            "6c0d6ae25036409ca94cc2c9ee42c85d",
            "d993c64f3a57403983c4ecaa54fc66f1",
            "fe68e53f61e7444f845b9703304cdc69",
            "2f2f0682d34548749324cb597655c274",
            "bde741ef746f469e9f69212471907591",
            "4977fe31f064438388c6272fb7c49a4c",
            "9c10c01387334ebdbf6e3e459b38c925",
            "504775b9b48744c18d73ceffb0ab8aa3"
          ]
        },
        "id": "2y0FvZxQ_WOY",
        "outputId": "cbdce1a1-1fee-47b6-8e59-599590e112af"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "/usr/local/lib/python3.10/dist-packages/huggingface_hub/utils/_auth.py:94: UserWarning: \n",
            "The secret `HF_TOKEN` does not exist in your Colab secrets.\n",
            "To authenticate with the Hugging Face Hub, create a token in your settings tab (https://huggingface.co/settings/tokens), set it as secret in your Google Colab and restart your session.\n",
            "You will be able to reuse this secret in all of your notebooks.\n",
            "Please note that authentication is recommended but still optional to access public models or datasets.\n",
            "  warnings.warn(\n"
          ]
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "Loading checkpoint shards:   0%|          | 0/2 [00:00<?, ?it/s]"
            ],
            "application/vnd.jupyter.widget-view+json": {
              "version_major": 2,
              "version_minor": 0,
              "model_id": "d331897cbe0b4c34ab9d34e9387ef4db"
            }
          },
          "metadata": {}
        }
      ],
      "source": [
        "# for more about Qwen2.5-Coder, see:\n",
        "# https://qwenlm.github.io/blog/qwen2.5-coder-family/\n",
        "\n",
        "# adapted from https://qwen.readthedocs.io/en/latest/inference/chat.html\n",
        "\n",
        "from transformers import pipeline\n",
        "\n",
        "pipe = pipeline('text-generation',\n",
        "                model = 'Qwen/Qwen2.5-Coder-7B-Instruct-AWQ',\n",
        "                device_map = 'auto')\n",
        "\n",
        "# depending on available VRAM, you may be able to use larger models:\n",
        "# https://huggingface.co/collections/Qwen/qwen25-coder-66eaa22e6f99801bf65b0c2f"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "schema = '''\n",
        "CREATE TABLE major (\n",
        "\tmajor VARCHAR(255),\n",
        "\tschool VARCHAR(255),\n",
        "\tCONSTRAINT PK_major PRIMARY KEY (major)\n",
        "\t);\n",
        "\n",
        "CREATE TABLE student (\n",
        "\tstudentid INT,\n",
        "\tname VARCHAR(255),\n",
        "\tmajor VARCHAR(255),\n",
        "\tyear INT,\n",
        "\tCONSTRAINT PK_student PRIMARY KEY (studentid),\n",
        "\tCONSTRAINT FK_student_to_major FOREIGN KEY (major)\n",
        "\t\tREFERENCES major(major)\n",
        "\t);\n",
        "\n",
        "CREATE TABLE course (\n",
        "\tcourseid INT,\n",
        "\ttopic VARCHAR(255),\n",
        "\ttitle VARCHAR(255),\n",
        "\tCONSTRAINT PK_course PRIMARY KEY (courseid)\n",
        "\t);\n",
        "\n",
        "CREATE TABLE takes (\n",
        "\tstudentid INT,\n",
        "\tcourseid INT,\n",
        "\tdate date,\n",
        "\tCONSTRAINT PK_takes PRIMARY KEY (studentid, courseid),\n",
        "\tCONSTRAINT FK_takes_to_student FOREIGN KEY (studentid)\n",
        "\t\tREFERENCES student(studentid),\n",
        "\tCONSTRAINT FK_takes_to_course FOREIGN KEY (courseid)\n",
        "\t\tREFERENCES course(courseid)\n",
        "\t);\n",
        "\n",
        "INSERT INTO major (major, school) VALUES ('COMM', 'Commerce');\n",
        "INSERT INTO major (major, school) VALUES ('CS', 'Engineering');\n",
        "INSERT INTO major (major, school) VALUES ('SE', 'Engineering');\n",
        "\n",
        "INSERT INTO student VALUES (100, 'Lucy', 'COMM', 4);\n",
        "INSERT INTO student VALUES (140, 'Brian', 'CS', 2);\n",
        "INSERT INTO student VALUES (130, 'Lindsey', 'SE', 4);\n",
        "\n",
        "INSERT INTO course VALUES (3200, 'RDBMS', 'Intro...');\n",
        "INSERT INTO course VALUES (7450, 'Stats', 'Adv...');\n",
        "INSERT INTO course VALUES (3600, 'Big Data', 'Intro...');\n",
        "INSERT INTO course VALUES (3460, 'Finance', 'Intro...');\n",
        "INSERT INTO course VALUES (4200, 'Global', 'Adv...');\n",
        "INSERT INTO course VALUES (3100, 'Global', 'Intro...');\n",
        "\n",
        "INSERT INTO takes VALUES (100, 3200, '2016-11-01');\n",
        "INSERT INTO takes VALUES (100, 7450, '2016-11-01');\n",
        "INSERT INTO takes VALUES (100, 4200, '2016-12-01');\n",
        "INSERT INTO takes VALUES (140, 3600, '2016-12-01');\n",
        "INSERT INTO takes VALUES (140, 3460, '2016-12-01');\n",
        "INSERT INTO takes VALUES (140, 4200, '2016-01-01');\n",
        "INSERT INTO takes VALUES (130, 3100, '2016-12-01');\n",
        "INSERT INTO takes VALUES (130, 3600, '2016-12-01');\n",
        "INSERT INTO takes VALUES (130, 3200, '2016-12-01');\n",
        "\n",
        "CREATE TABLE engmajor (\n",
        "\tmajor VARCHAR(255),\n",
        "\tschool VARCHAR(255),\n",
        "\tCONSTRAINT PK_engmajor PRIMARY KEY (major)\n",
        "\t);\n",
        "\n",
        "INSERT INTO engmajor (major, school) VALUES ('CS', 'Engineering');\n",
        "INSERT INTO engmajor (major, school) VALUES ('SE', 'Engineering');\n",
        "\n",
        "CREATE TABLE students (\n",
        "\tstudentid INT,\n",
        "\tname VARCHAR(255),\n",
        "\tmajor VARCHAR(255),\n",
        "\tyear INT,\n",
        "\tCONSTRAINT PK_students PRIMARY KEY (studentid),\n",
        "\tCONSTRAINT FK_students_to_major FOREIGN KEY (major)\n",
        "\t\tREFERENCES major(major)\n",
        "\t);\n",
        "\n",
        "CREATE TABLE studentemp (\n",
        "\tstudentident INT,\n",
        "\thourlysalary INT,\n",
        "\tCONSTRAINT PK_studentemp PRIMARY KEY (studentident)\n",
        "\t);\n",
        "\n",
        "INSERT INTO studentemp VALUES (150, 12);\n",
        "INSERT INTO studentemp VALUES (130, 15);\n",
        "INSERT INTO studentemp VALUES (190, 20);\n",
        "\n",
        "CREATE TABLE morestudent (\n",
        "\tstudentid INT,\n",
        "\tname VARCHAR(255),\n",
        "\tmajor VARCHAR(255),\n",
        "\tyear INT,\n",
        "\tCONSTRAINT PK_morestudent PRIMARY KEY (studentid, major),\n",
        "\tCONSTRAINT FK_morestudent_to_major FOREIGN KEY (major)\n",
        "\t\tREFERENCES major(major)\n",
        "\t);\n",
        "\n",
        "INSERT INTO morestudent VALUES (150, 'Tim', 'COMM', 3);\n",
        "INSERT INTO morestudent VALUES (140, 'Brian', 'CS', 2);\n",
        "INSERT INTO morestudent VALUES (160, 'Will', 'COMM', 2);\n",
        "'''\n",
        "\n",
        "prompt = '''\n",
        "### Task\n",
        "Consider the database schema below and answer the question that follows.\n",
        "\n",
        "### Database Schema\n",
        "This query will run on a database whose schema is represented in this string:\n",
        "\n",
        "```sql{schema}```\n",
        "\n",
        "### SQL\n",
        "Given the database schema, here is the SQL query that answers `{question}`:\n",
        "'''"
      ],
      "metadata": {
        "id": "TwhmCIew0nW5"
      },
      "execution_count": 3,
      "outputs": []
    },
    {
      "cell_type": "code",
      "execution_count": 4,
      "metadata": {
        "id": "mJvQcgroUHrP",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "ee46a5df-fa0a-4d8d-db88-6c1f414e6027"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "To determine how many courses each student in the `STUDENT` table is currently taking, we can use the following SQL query:\n",
            "\n",
            "```sql\n",
            "SELECT s.studentid, s.name, COUNT(t.courseid) AS number_of_courses\n",
            "FROM student s\n",
            "JOIN takes t ON s.studentid = t.studentid\n",
            "GROUP BY s.studentid, s.name;\n",
            "```\n",
            "\n",
            "This query works as follows:\n",
            "- It joins the `student` table (`s`) with the `takes` table (`t`) on the `studentid` field to get the courses taken by each student.\n",
            "- It groups the results by `studentid` and `name` to ensure that the count of courses is calculated for each individual student.\n",
            "- It uses the `COUNT` function to count the number of `courseid` entries for each student, which represents the number of courses they are currently taking.\n",
            "- The result includes the `studentid`, `name`, and the `number_of_courses` for each student.\n"
          ]
        }
      ],
      "source": [
        "question = 'How many courses are each of the students in the STUDENT table currently taking?'\n",
        "\n",
        "# the default system message will be used\n",
        "messages = [{'role': 'user', 'content': prompt.format(question = question,\n",
        "                                                      schema = schema)}]\n",
        "\n",
        "response_message = pipe(messages, max_new_tokens = 512)[0]['generated_text'][-1]\n",
        "\n",
        "print(response_message['content'])"
      ]
    }
  ],
  "metadata": {
    "accelerator": "GPU",
    "colab": {
      "gpuType": "T4",
      "provenance": []
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    },
    "language_info": {
      "name": "python"
    },
    "widgets": {
      "application/vnd.jupyter.widget-state+json": {
        "d331897cbe0b4c34ab9d34e9387ef4db": {
          "model_module": "@jupyter-widgets/controls",
          "model_name": "HBoxModel",
          "model_module_version": "1.5.0",
          "state": {
            "_dom_classes": [],
            "_model_module": "@jupyter-widgets/controls",
            "_model_module_version": "1.5.0",
            "_model_name": "HBoxModel",
            "_view_count": null,
            "_view_module": "@jupyter-widgets/controls",
            "_view_module_version": "1.5.0",
            "_view_name": "HBoxView",
            "box_style": "",
            "children": [
              "IPY_MODEL_c2843f3c972e46c992f3cca451faced8",
              "IPY_MODEL_6ab2e324e1cc427aa4acc17081dd8494",
              "IPY_MODEL_6c0d6ae25036409ca94cc2c9ee42c85d"
            ],
            "layout": "IPY_MODEL_d993c64f3a57403983c4ecaa54fc66f1"
          }
        },
        "c2843f3c972e46c992f3cca451faced8": {
          "model_module": "@jupyter-widgets/controls",
          "model_name": "HTMLModel",
          "model_module_version": "1.5.0",
          "state": {
            "_dom_classes": [],
            "_model_module": "@jupyter-widgets/controls",
            "_model_module_version": "1.5.0",
            "_model_name": "HTMLModel",
            "_view_count": null,
            "_view_module": "@jupyter-widgets/controls",
            "_view_module_version": "1.5.0",
            "_view_name": "HTMLView",
            "description": "",
            "description_tooltip": null,
            "layout": "IPY_MODEL_fe68e53f61e7444f845b9703304cdc69",
            "placeholder": "​",
            "style": "IPY_MODEL_2f2f0682d34548749324cb597655c274",
            "value": "Loading checkpoint shards: 100%"
          }
        },
        "6ab2e324e1cc427aa4acc17081dd8494": {
          "model_module": "@jupyter-widgets/controls",
          "model_name": "FloatProgressModel",
          "model_module_version": "1.5.0",
          "state": {
            "_dom_classes": [],
            "_model_module": "@jupyter-widgets/controls",
            "_model_module_version": "1.5.0",
            "_model_name": "FloatProgressModel",
            "_view_count": null,
            "_view_module": "@jupyter-widgets/controls",
            "_view_module_version": "1.5.0",
            "_view_name": "ProgressView",
            "bar_style": "success",
            "description": "",
            "description_tooltip": null,
            "layout": "IPY_MODEL_bde741ef746f469e9f69212471907591",
            "max": 2,
            "min": 0,
            "orientation": "horizontal",
            "style": "IPY_MODEL_4977fe31f064438388c6272fb7c49a4c",
            "value": 2
          }
        },
        "6c0d6ae25036409ca94cc2c9ee42c85d": {
          "model_module": "@jupyter-widgets/controls",
          "model_name": "HTMLModel",
          "model_module_version": "1.5.0",
          "state": {
            "_dom_classes": [],
            "_model_module": "@jupyter-widgets/controls",
            "_model_module_version": "1.5.0",
            "_model_name": "HTMLModel",
            "_view_count": null,
            "_view_module": "@jupyter-widgets/controls",
            "_view_module_version": "1.5.0",
            "_view_name": "HTMLView",
            "description": "",
            "description_tooltip": null,
            "layout": "IPY_MODEL_9c10c01387334ebdbf6e3e459b38c925",
            "placeholder": "​",
            "style": "IPY_MODEL_504775b9b48744c18d73ceffb0ab8aa3",
            "value": " 2/2 [00:22&lt;00:00, 10.03s/it]"
          }
        },
        "d993c64f3a57403983c4ecaa54fc66f1": {
          "model_module": "@jupyter-widgets/base",
          "model_name": "LayoutModel",
          "model_module_version": "1.2.0",
          "state": {
            "_model_module": "@jupyter-widgets/base",
            "_model_module_version": "1.2.0",
            "_model_name": "LayoutModel",
            "_view_count": null,
            "_view_module": "@jupyter-widgets/base",
            "_view_module_version": "1.2.0",
            "_view_name": "LayoutView",
            "align_content": null,
            "align_items": null,
            "align_self": null,
            "border": null,
            "bottom": null,
            "display": null,
            "flex": null,
            "flex_flow": null,
            "grid_area": null,
            "grid_auto_columns": null,
            "grid_auto_flow": null,
            "grid_auto_rows": null,
            "grid_column": null,
            "grid_gap": null,
            "grid_row": null,
            "grid_template_areas": null,
            "grid_template_columns": null,
            "grid_template_rows": null,
            "height": null,
            "justify_content": null,
            "justify_items": null,
            "left": null,
            "margin": null,
            "max_height": null,
            "max_width": null,
            "min_height": null,
            "min_width": null,
            "object_fit": null,
            "object_position": null,
            "order": null,
            "overflow": null,
            "overflow_x": null,
            "overflow_y": null,
            "padding": null,
            "right": null,
            "top": null,
            "visibility": null,
            "width": null
          }
        },
        "fe68e53f61e7444f845b9703304cdc69": {
          "model_module": "@jupyter-widgets/base",
          "model_name": "LayoutModel",
          "model_module_version": "1.2.0",
          "state": {
            "_model_module": "@jupyter-widgets/base",
            "_model_module_version": "1.2.0",
            "_model_name": "LayoutModel",
            "_view_count": null,
            "_view_module": "@jupyter-widgets/base",
            "_view_module_version": "1.2.0",
            "_view_name": "LayoutView",
            "align_content": null,
            "align_items": null,
            "align_self": null,
            "border": null,
            "bottom": null,
            "display": null,
            "flex": null,
            "flex_flow": null,
            "grid_area": null,
            "grid_auto_columns": null,
            "grid_auto_flow": null,
            "grid_auto_rows": null,
            "grid_column": null,
            "grid_gap": null,
            "grid_row": null,
            "grid_template_areas": null,
            "grid_template_columns": null,
            "grid_template_rows": null,
            "height": null,
            "justify_content": null,
            "justify_items": null,
            "left": null,
            "margin": null,
            "max_height": null,
            "max_width": null,
            "min_height": null,
            "min_width": null,
            "object_fit": null,
            "object_position": null,
            "order": null,
            "overflow": null,
            "overflow_x": null,
            "overflow_y": null,
            "padding": null,
            "right": null,
            "top": null,
            "visibility": null,
            "width": null
          }
        },
        "2f2f0682d34548749324cb597655c274": {
          "model_module": "@jupyter-widgets/controls",
          "model_name": "DescriptionStyleModel",
          "model_module_version": "1.5.0",
          "state": {
            "_model_module": "@jupyter-widgets/controls",
            "_model_module_version": "1.5.0",
            "_model_name": "DescriptionStyleModel",
            "_view_count": null,
            "_view_module": "@jupyter-widgets/base",
            "_view_module_version": "1.2.0",
            "_view_name": "StyleView",
            "description_width": ""
          }
        },
        "bde741ef746f469e9f69212471907591": {
          "model_module": "@jupyter-widgets/base",
          "model_name": "LayoutModel",
          "model_module_version": "1.2.0",
          "state": {
            "_model_module": "@jupyter-widgets/base",
            "_model_module_version": "1.2.0",
            "_model_name": "LayoutModel",
            "_view_count": null,
            "_view_module": "@jupyter-widgets/base",
            "_view_module_version": "1.2.0",
            "_view_name": "LayoutView",
            "align_content": null,
            "align_items": null,
            "align_self": null,
            "border": null,
            "bottom": null,
            "display": null,
            "flex": null,
            "flex_flow": null,
            "grid_area": null,
            "grid_auto_columns": null,
            "grid_auto_flow": null,
            "grid_auto_rows": null,
            "grid_column": null,
            "grid_gap": null,
            "grid_row": null,
            "grid_template_areas": null,
            "grid_template_columns": null,
            "grid_template_rows": null,
            "height": null,
            "justify_content": null,
            "justify_items": null,
            "left": null,
            "margin": null,
            "max_height": null,
            "max_width": null,
            "min_height": null,
            "min_width": null,
            "object_fit": null,
            "object_position": null,
            "order": null,
            "overflow": null,
            "overflow_x": null,
            "overflow_y": null,
            "padding": null,
            "right": null,
            "top": null,
            "visibility": null,
            "width": null
          }
        },
        "4977fe31f064438388c6272fb7c49a4c": {
          "model_module": "@jupyter-widgets/controls",
          "model_name": "ProgressStyleModel",
          "model_module_version": "1.5.0",
          "state": {
            "_model_module": "@jupyter-widgets/controls",
            "_model_module_version": "1.5.0",
            "_model_name": "ProgressStyleModel",
            "_view_count": null,
            "_view_module": "@jupyter-widgets/base",
            "_view_module_version": "1.2.0",
            "_view_name": "StyleView",
            "bar_color": null,
            "description_width": ""
          }
        },
        "9c10c01387334ebdbf6e3e459b38c925": {
          "model_module": "@jupyter-widgets/base",
          "model_name": "LayoutModel",
          "model_module_version": "1.2.0",
          "state": {
            "_model_module": "@jupyter-widgets/base",
            "_model_module_version": "1.2.0",
            "_model_name": "LayoutModel",
            "_view_count": null,
            "_view_module": "@jupyter-widgets/base",
            "_view_module_version": "1.2.0",
            "_view_name": "LayoutView",
            "align_content": null,
            "align_items": null,
            "align_self": null,
            "border": null,
            "bottom": null,
            "display": null,
            "flex": null,
            "flex_flow": null,
            "grid_area": null,
            "grid_auto_columns": null,
            "grid_auto_flow": null,
            "grid_auto_rows": null,
            "grid_column": null,
            "grid_gap": null,
            "grid_row": null,
            "grid_template_areas": null,
            "grid_template_columns": null,
            "grid_template_rows": null,
            "height": null,
            "justify_content": null,
            "justify_items": null,
            "left": null,
            "margin": null,
            "max_height": null,
            "max_width": null,
            "min_height": null,
            "min_width": null,
            "object_fit": null,
            "object_position": null,
            "order": null,
            "overflow": null,
            "overflow_x": null,
            "overflow_y": null,
            "padding": null,
            "right": null,
            "top": null,
            "visibility": null,
            "width": null
          }
        },
        "504775b9b48744c18d73ceffb0ab8aa3": {
          "model_module": "@jupyter-widgets/controls",
          "model_name": "DescriptionStyleModel",
          "model_module_version": "1.5.0",
          "state": {
            "_model_module": "@jupyter-widgets/controls",
            "_model_module_version": "1.5.0",
            "_model_name": "DescriptionStyleModel",
            "_view_count": null,
            "_view_module": "@jupyter-widgets/base",
            "_view_module_version": "1.2.0",
            "_view_name": "StyleView",
            "description_width": ""
          }
        }
      }
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}