final.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "rk8bAu2IQWBZ"
      },
      "source": [
        "## Data Creation"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "u3pmuzD1nWfZ"
      },
      "source": [
        "# data function to put into class "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "id": "DBhZwsv4nB1d"
      },
      "outputs": [],
      "source": [
        "import pandas as pd\n",
        "import numpy as np\n",
        "import sqlite3\n",
        "from db_builder import SchoolDataGenerator\n",
        "from class_test import QueryTest\n",
        "\n",
        "# Instantiate the class\n",
        "school_data = SchoolDataGenerator()\n",
        "test_instance = QueryTest()\n",
        "\n",
        "# Generate data & create the database\n",
        "school_data.generate_data()\n",
        "school_data.populate_db()\n",
        "\n",
        "# Global variable \n",
        "sql = school_data.sql\n",
        "conn = school_data.conn"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ogPHFUpa8kbh"
      },
      "source": [
        "# Table Schemas\n",
        "\n",
        "<table>\n",
        "  <tr>\n",
        "    <td>\n",
        "      <h3>students Table Schema</h3>\n",
        "      <table border=\"1\">\n",
        "        <tr><th>Column Name</th><th>Data Type</th></tr>\n",
        "        <tr><td>student_id</td><td>INTEGER</td></tr>\n",
        "        <tr><td>first_name</td><td>TEXT</td></tr>\n",
        "        <tr><td>last_name</td><td>TEXT</td></tr>\n",
        "        <tr><td>grade_level</td><td>TEXT</td></tr>\n",
        "        <tr><td>email</td><td>TEXT</td></tr>\n",
        "      </table>\n",
        "    </td>\n",
        "    <td>\n",
        "      <h3>assignments Table Schema</h3>\n",
        "      <table border=\"1\">\n",
        "        <tr><th>Column Name</th><th>Data Type</th></tr>\n",
        "        <tr><td>assignment_id</td><td>INTEGER</td></tr>\n",
        "        <tr><td>assignment_name</td><td>TEXT</td></tr>\n",
        "        <tr><td>due_date</td><td>TEXT</td></tr>\n",
        "      </table>\n",
        "    </td>\n",
        "  </tr>\n",
        "  <tr>\n",
        "    <td>\n",
        "      <h3>grades Table Schema</h3>\n",
        "      <table border=\"1\">\n",
        "        <tr><th>Column Name</th><th>Data Type</th></tr>\n",
        "        <tr><td>grade_id</td><td>INTEGER</td></tr>\n",
        "        <tr><td>student_id</td><td>INTEGER</td></tr>\n",
        "        <tr><td>assignment_id</td><td>INTEGER</td></tr>\n",
        "        <tr><td>grade</td><td>TEXT</td></tr>\n",
        "        <tr><td>numeric_score</td><td>INTEGER</td></tr>\n",
        "      </table>\n",
        "    </td>\n",
        "    <td>\n",
        "      <h3>participation_behavior Table Schema</h3>\n",
        "      <table border=\"1\">\n",
        "        <tr><th>Column Name</th><th>Data Type</th></tr>\n",
        "        <tr><td>record_id</td><td>INTEGER</td></tr>\n",
        "        <tr><td>student_id</td><td>INTEGER</td></tr>\n",
        "        <tr><td>attendance</td><td>TEXT</td></tr>\n",
        "        <tr><td>participation_grade</td><td>INTEGER</td></tr>\n",
        "        <tr><td>behavior_grade</td><td>INTEGER</td></tr>\n",
        "      </table>\n",
        "    </td>\n",
        "  </tr>\n",
        "</table>\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "xoE479XaD3gS"
      },
      "source": [
        "# Exercise 1: Basic Queries"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "gDQb1Pt6kajL"
      },
      "source": [
        "## Basic Query Objectives\n",
        "\n",
        "In this exercise, you will:\n",
        "\n",
        "1. **Use the `SELECT` Statement:**\n",
        "   - Retrieve specific columns from a table.\n",
        "\n",
        "2. **Filter Results with the `WHERE` Clause:**\n",
        "   - Apply conditions to filter the data returned by the query.\n",
        "\n",
        "3. **Sort Data with the `ORDER BY` Clause:**\n",
        "   - Arrange the results in a specified order.\n",
        "\n",
        "4. **Combine All Three Techniques:**\n",
        "   - Create a query that integrates `SELECT`, `WHERE`, and `ORDER BY` to retrieve and organize data according to specified criteria.\n",
        "\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "rsLRjKlVEGkx"
      },
      "source": [
        "For this excercise we will use the Students table\n",
        "\n",
        "### `students` Table Schema\n",
        "\n",
        "| Column Name | Data Type |\n",
        "|-------------|-----------|\n",
        "| student_id | INTEGER |\n",
        "| first_name | TEXT |\n",
        "| last_name | TEXT |\n",
        "| grade_level | TEXT |\n",
        "| email | TEXT |"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "GX5Bygm4ERBU"
      },
      "source": [
        "Preform a Select Query:\n",
        "\n",
        "- `SELECT *`: This part of the query specifies that you want to retrieve all columns from the table.\n",
        "- `FROM students`: This specifies the table from which to retrieve the data, which is `students` in this case.\n",
        "- The semicolon (`;`) at the end of the query denotes the end of the SQL command.\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "id": "fIMiNx0r8xyL",
        "outputId": "2e198fe1-5bb9-45c3-b1f1-c0a6070e5593"
      },
      "outputs": [],
      "source": [
        "select_query = \"\"\"\n",
        "YOUR QUERY HERE\n",
        "\"\"\"\n",
        "\n",
        "sql(select_query).head()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "d-5AMpPPS9g3",
        "outputId": "54d271f9-ddce-4eda-dc7e-cea193ba2a79"
      },
      "outputs": [],
      "source": [
        "test_instance.select_test(select_query)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "37TA9Wg0G_w1"
      },
      "source": [
        "## Perform a Select Query with Filtering:\n",
        "\n",
        "- `SELECT *`: This part of the query retrieves all columns from the table.\n",
        "- `FROM students`: Specifies the table from which to retrieve the data, which is `students`.\n",
        "- `WHERE grade_level = '12th'`: Filters the results to include only rows where the `grade_level` column is equal to `'12th'`.\n",
        "- The semicolon (`;`) at the end of the query denotes the end of the SQL command.\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 425
        },
        "id": "iD9XlBm_9cFP",
        "outputId": "1646d354-57f8-4ca6-a47d-00802b0a47c2"
      },
      "outputs": [],
      "source": [
        "where_query = \"\"\"\n",
        "YOUR QUERY HERE\n",
        "\"\"\"\n",
        "\n",
        "sql(where_query)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "cDz4qoWDUjFR",
        "outputId": "287c17e8-7696-4679-d906-d1717dcc79cb"
      },
      "outputs": [],
      "source": [
        "test_instance.where_test(where_query)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "a9FuzDxqHHMW"
      },
      "source": [
        "## Perform a Select Query with Sorting:\n",
        "\n",
        "- `SELECT *`: This part of the query retrieves all columns from the table.\n",
        "- `FROM students`: Specifies the table from which to retrieve the data, which is `students`.\n",
        "- `ORDER BY last_name ASC`: Sorts the results by the `last_name` column in ascending order (`ASC`).\n",
        "- The semicolon (`;`) at the end of the query denotes the end of the SQL command.\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "id": "qmmbO6_G_eQH",
        "outputId": "c7f5cd72-bd91-44b8-c662-9219f494ba6a"
      },
      "outputs": [],
      "source": [
        "orderby_query = \"\"\"\n",
        "YOUR QUERY HERE\n",
        "\"\"\"\n",
        "\n",
        "sql(orderby_query).head()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "JhO4mItElv7B",
        "outputId": "ca61b168-988d-420b-e563-0a34d61bb2c8"
      },
      "outputs": [],
      "source": [
        "test_instance.orderby_test(orderby_query, order='ASC')"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "N6rdaWFBHMtr"
      },
      "source": [
        "## Perform a Select Query with Filtering and Sorting:\n",
        "\n",
        "- `SELECT *`: Retrieves all columns from the table.\n",
        "- `FROM students`: Specifies the table from which to retrieve the data, which is `students`.\n",
        "- `WHERE grade_level = '12th'`: Filters the results to include only rows where the `grade_level` column is `'12th'`.\n",
        "- `ORDER BY last_name ASC`: Sorts the filtered results by the `last_name` column in ascending order (`ASC`).\n",
        "- The semicolon (`;`) at the end of the query denotes the end of the SQL command.\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "id": "LXRQCzra_mez",
        "outputId": "6183a561-17c8-4ef8-dba0-14799ab24161"
      },
      "outputs": [],
      "source": [
        "multi_query = \"\"\"\n",
        "YOUR QUERY HERE\n",
        "\"\"\"\n",
        "\n",
        "sql(multi_query).head()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "siwyDYNMZp4U",
        "outputId": "0ba592e7-c295-44f5-c9f2-7e628410fa58"
      },
      "outputs": [],
      "source": [
        "test_instance.multi_query_test(multi_query)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "lZPhglwYHYlr"
      },
      "source": [
        "## What We Learned\n",
        "\n",
        "1. **Basic Query Retrieval:**\n",
        "   - **Query:** `SELECT * FROM students;`\n",
        "   - **Learning:** This query retrieves all columns and rows from the `students` table. It shows how to use the `SELECT` statement to access the entire dataset from a specified table.\n",
        "\n",
        "2. **Filtering Results:**\n",
        "   - **Query:** `SELECT * FROM students WHERE grade_level = '12th';`\n",
        "   - **Learning:** This query filters the results to include only those rows where the `grade_level` is `'12th'`. It demonstrates how to use the `WHERE` clause to specify conditions and limit the data retrieved based on criteria.\n",
        "\n",
        "3. **Sorting Results:**\n",
        "   - **Query:** `SELECT * FROM students ORDER BY last_name ASC;`\n",
        "   - **Learning:** This query retrieves all rows from the `students` table and sorts them by the `last_name` column in ascending order. It shows how to use the `ORDER BY` clause to arrange the data in a specified order.\n",
        "\n",
        "4. **Combining Filtering and Sorting:**\n",
        "   - **Query:** `SELECT * FROM students WHERE grade_level = '12th' ORDER BY last_name ASC;`\n",
        "   - **Learning:** This query combines both filtering and sorting. It retrieves rows where `grade_level` is `'12th'` and then sorts these filtered results by `last_name` in ascending order. This demonstrates how to integrate multiple clauses to refine and organize the data retrieval.\n",
        "\n",
        "By performing these queries, we learned how to retrieve data from a table, apply filters to focus on specific records, sort the results, and combine these techniques to produce more targeted and organized data output.\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "UAvB16AFxyKR"
      },
      "source": [
        "# Exercise 2\n",
        "\n",
        "The goal of this exercise is to perform 2 of the 4 different types of SQL joins.\n",
        "\n",
        "The 4 types of joins are:\n",
        "- Inner Join\n",
        "- Left Join\n",
        "- Right Join\n",
        "- Full Outer Join\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "OIwZGAR4w3ob"
      },
      "source": [
        "Left Join:\n",
        "\n",
        "Using the two tables, `students` and `grades`. Your task is to write an SQL query to retrieve a list of all students along with their corresponding grades for any assignments they have completed.\n",
        "\n",
        "Left Join Instructions:\n",
        "\n",
        "1. Use the `students` table as the primary table.\n",
        "1. Perform a `LEFT JOIN` with the `grades` table on the common `student_id` column.\n",
        "1. Select the following columns in your query:\n",
        "  - `student_id` from the `students` table\n",
        "  - `first_name` from the `students` table\n",
        "  - `last_name` from the `students` table\n",
        "  - `assignment_id` from the `grades` table\n",
        "  - `grade` from the `grades` table"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 10,
      "metadata": {
        "id": "O0fDhR0J2DFq"
      },
      "outputs": [],
      "source": [
        "left_join_query = \"\"\"\n",
        "YOUR QUERY HERE\n",
        "\"\"\"\n",
        "\n",
        "left_join = sql(left_join_query)\n",
        "left_join"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "xtxCslM9qEjQ",
        "outputId": "ee17009e-dfe0-4abf-e8cd-edda4de9c8a2"
      },
      "outputs": [],
      "source": [
        "test_instance.left_join_test(left_join_query)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "JNSUBNlxw9wN"
      },
      "source": [
        "Inner join Instructions:\n",
        "1. Use the students table as the primary table.\n",
        "1. Perform an INNER JOIN with the grades table on the common student_id column.\n",
        "1. Select the following columns in your query:\n",
        "\n",
        "  - student_id from the students table\n",
        "  - first_name from the students table\n",
        "  - last_name from the students table\n",
        "  - assignment_id from the grades table\n",
        "  - grade from the grades table\n",
        "\n",
        "1. Ensure that only the records where there is a matching student_id in both the students and grades tables are included in your result."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "id": "1bZxKJPyQEUp",
        "outputId": "c68992b1-a06a-4b7c-98b5-5a506979656a"
      },
      "outputs": [],
      "source": [
        "inner_join_query = \"\"\"\n",
        "YOUR QUERY HERE\n",
        "\"\"\"\n",
        "\n",
        "inner_join = sql(inner_join_query)\n",
        "inner_join"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "BfQk9Qhittb5",
        "outputId": "c18623a2-c88b-4bec-fa66-edc2580af36d"
      },
      "outputs": [],
      "source": [
        "test_instance.inner_join_test(inner_join_query)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "8UPfC4sgkCuU"
      },
      "source": [
        "# Exercise 3\n",
        "\n",
        "### Goals for this exercise\n",
        "The goal of this exercuse is to:\n",
        "- Insert a new record into the `students` table with specific values.\n",
        "- Delete the new record in the `students` table.\n",
        "- UPDATE\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "t0sp8AQ4xFq-"
      },
      "source": [
        "### Inserting a Record\n",
        "Insert a new record into the `students` table with specific values.\n",
        "\n",
        "### Step-by-Step Guide\n",
        "\n",
        "1. **Identify the Table:**\n",
        "   - Determine which table you need to `insert` data into. In this case, it is the `students` table.\n",
        "\n",
        "2. **Specify the Columns:**\n",
        "   - List the columns that will receive the new data. For this task, the columns are `student_id` = 31, `first_name` = John, `last_name` = Doe, `grade_level` = 12, and `email` = student31@school.com.\n",
        "\n",
        "3. **Prepare the VALUES Clause:**\n",
        "   - Decide the values you want to insert for each column. Ensure that the values match the data types expected by the columns.\n",
        "\n",
        "4. **Write the SQL Query:**\n",
        "   - Use the `INSERT INTO` statement to specify the table and columns.\n",
        "   - Follow with the `VALUES` keyword to provide the values.\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 15,
      "metadata": {
        "id": "CSY0UdZThguJ"
      },
      "outputs": [],
      "source": [
        "insert_query = \"\"\"\n",
        "YOUR QUERY HERE\n",
        "\"\"\"\n",
        "\n",
        "conn.execute(insert_query)\n",
        "conn.commit()\n",
        "\n",
        "student_31 = sql(\"SELECT * FROM students WHERE student_id = 31\")\n",
        "student_31"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "test_instance.insert_test(insert_query)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "lv0Btmw-kIZW"
      },
      "source": [
        "### Deleting a Record\n",
        "Remove a specific record from the `students` table based on the `student_id`.\n",
        "\n",
        "### Step-by-Step Guide\n",
        "\n",
        "1. **Identify the Table:**\n",
        "   - Determine from which table you need to delete data. In this case, it is the `students` table.\n",
        "\n",
        "2. **Specify the Condition:**\n",
        "   - Identify the specific record to delete by setting a condition. For this task, the condition is `student_id = 31`.\n",
        "\n",
        "3. **Write the SQL Query:**\n",
        "   - Use the `DELETE FROM` statement to specify the table.\n",
        "   - Follow with a `WHERE` clause to define the condition for which row to delete. In this case, it is `WHERE student_id = 31`.\n",
        "\n",
        "4. **Confirm the Deletion:**\n",
        "   - Always check that your condition is correct to avoid accidentally deleting the wrong data or more records than intended.\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 89
        },
        "id": "fYMz66Uajiux",
        "outputId": "3c50656e-812a-4dc7-f1b6-532f26cfde94"
      },
      "outputs": [],
      "source": [
        "delete_query = \"\"\"\n",
        "YOUR QUERY HERE\n",
        "\"\"\"\n",
        "\n",
        "conn.execute(delete_query)\n",
        "conn.commit()\n",
        "\n",
        "student_31_check = sql(\"SELECT * FROM students WHERE student_id = 31\")\n",
        "student_31_check"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "test_instance.delete_test(delete_query)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 37
        },
        "id": "LOYUTPcMjeKT",
        "outputId": "3e26c8b8-9654-4211-89ec-48105b05c2e1"
      },
      "outputs": [],
      "source": [
        "def insert_query():\n",
        "  insert_query = \"\"\"\n",
        "YOUR QUERY HERE\n",
        "\"\"\"\n",
        "\n",
        "  conn.execute(insert_query)\n",
        "  conn.commit()\n",
        "  return insert_query\n",
        "\n",
        "insert_query()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "SLzbaoNs0DFz"
      },
      "source": [
        "We have added back student_id 31 John Doe witht he above code."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "1sqK5oxk0Qip"
      },
      "source": [
        "### Updating a Record\n",
        "\n",
        "In this scenario, we mistakenly entered John Doe's name incorrectly. Instead of adding a new record, we can modify the existing one and update the first name to `Jane`.\n",
        "\n",
        "### Step-by-Step Guide\n",
        "\n",
        "1. **Identify the Table:**\n",
        "   - Determine which table contains the record you want to update. In this case, it's the `students` table.\n",
        "\n",
        "2. **Use the `UPDATE` Clause:**\n",
        "   - The `UPDATE` clause allows you to modify existing records in a table.\n",
        "\n",
        "3. **Set the New Value:**\n",
        "   - Use the `SET` keyword to specify the column you want to update and the new value. Here, you'll set `first_name` to `Jane`.\n",
        "\n",
        "4. **Apply a Condition:**\n",
        "   - Use the `WHERE` clause to identify the specific record you want to update. For this task, update the record where `student_id = 31`.\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 89
        },
        "id": "DrtH2lI3jOMw",
        "outputId": "f74470d5-2340-4955-df36-d217d2ab6ace"
      },
      "outputs": [],
      "source": [
        "update_query = \"\"\"\n",
        "YOUR QUERY HERE\n",
        "\"\"\"\n",
        "\n",
        "conn.execute(update_query)\n",
        "conn.commit()\n",
        "\n",
        "student_31 = sql(\"SELECT * FROM students WHERE student_id = 31\")\n",
        "student_31"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "test_instance.update_query_test(update_query)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "test_instance.delete_query()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 53
        },
        "id": "JAb4Hk7NkceJ",
        "outputId": "f067eec3-125b-45ed-a899-c5ee353e221a"
      },
      "outputs": [],
      "source": [
        "query = \"\"\"\n",
        "YOUR QUERY HERE\n",
        "\"\"\"\n",
        "\n",
        "sql(query)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "f5gBEG455MvW"
      },
      "source": [
        "# exercise 4\n",
        "\n",
        "Advanced Queries and Filtering\n",
        "Objective: Deepen understanding of SQL querying with complex filters and aggregations.\n",
        "\n",
        "Topics:\n",
        "\n",
        "Complex Filtering:\n",
        "- Logical operators (AND, OR, NOT).\n",
        "- BETWEEN, IN, and LIKE operators.\n",
        "Aggregations:\n",
        "- GROUP BY clause.\n",
        "- Aggregate functions (COUNT, SUM, AVG, MAX, MIN)\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "zMnkiTg_5R8E"
      },
      "source": [
        "### Writing a Query with a using a logical operator\n",
        "\n",
        "Write a SQL query to retrieve a list of 12th-grade students along with their grades, including those who might not have a grade recorded yet.\n",
        "\n",
        "### Instructions:\n",
        "1. **Use a `LEFT JOIN`** to combine the `students` table with the `grades` table. This will ensure that all students are included in the result, even if they don't have a grade recorded.\n",
        "2. **Filter for 12th-grade students** by setting a condition on the `grade_level` column.\n",
        "3. **Include only students who have an 'A' or 'B' grade**, or students with no grade at all.\n",
        "4. **Select the following columns** to be returned in the result:\n",
        "   - `student_id`, `first_name`, `last_name`, `grade_level` from the `students` table.\n",
        "   - `grade` from the `grades` table."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "id": "fEJvzqYk3_BE",
        "outputId": "fbad056c-b6a2-496a-b038-fce24e5e9866"
      },
      "outputs": [],
      "source": [
        "Logical_operators = \"\"\"\n",
        "YOUR QUERY HERE\n",
        "\"\"\"\n",
        "\n",
        "sql(Logical_operators).head()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "test_instance.logical_operators_test(Logical_operators)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "eLg7Gcax7ZgZ"
      },
      "source": [
        "### Writing a Query with BETWEEN, IN, and LIKE\n",
        "\n",
        "Write a SQL query to retrieve student information from the `students` table along with their grades from the `grades` table. Use the following guidelines:\n",
        "\n",
        "1. **Select Columns**: Retrieve the `student_id`, `first_name`, `last_name`, and `grade_level` from the `students` table. Also retrieve the `grade` from the `grades` table.\n",
        "2. **Join**: Use an `INNER JOIN` to connect the `students` table with the `grades` table on the `student_id` column.\n",
        "3. **Filter Conditions**:\n",
        "   - Only include students whose `student_id` is between 1 and 7.\n",
        "   - Filter the students so that their `first_name` starts with the letter 'A' or 'J'.\n",
        "   - Only include students whose grades are either 'A', 'B', or 'C'.\n",
        "\n",
        "Write the query to meet all of the above conditions.\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 394
        },
        "id": "GvwdBYS47igS",
        "outputId": "5e581afd-4733-408b-db3f-f253576774c8"
      },
      "outputs": [],
      "source": [
        "filter_query = \"\"\"\n",
        "YOUR QUERY HERE\n",
        "\"\"\"\n",
        "\n",
        "filter_result = sql(filter_query)\n",
        "filter_result"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "test_instance.filter_query_test(filter_query)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "HTo-gz8LbnWq"
      },
      "source": [
        "### Write a Group By Query Averaging Grades\n",
        "\n",
        "Write a SQL query to calculate the average numeric grade for each `grade_level`. Follow these steps:\n",
        "\n",
        "1. **Select Columns**:\n",
        "   - Retrieve `grade_level` from the `students` table.\n",
        "   - Calculate the average of the numeric score from the `grades` table and label it as `average_grade`.\n",
        "\n",
        "2. **Join Tables**:\n",
        "   - Use an `INNER JOIN` to combine the `students` table with the `grades` table based on the `student_id` column.\n",
        "\n",
        "3. **Group By**:\n",
        "   - Group the results by `grade_level` from the `students` table."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 143
        },
        "id": "oWZLqnk7bnqa",
        "outputId": "3da6389a-32c8-495a-dc95-c666f5c1370c"
      },
      "outputs": [],
      "source": [
        "groupby_query = \"\"\"\n",
        "YOUR QUERY HERE\n",
        "\"\"\"\n",
        "\n",
        "groupby_result = sql(groupby_query)\n",
        "groupby_result"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "test_instance.groupby_query_test(groupby_query)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "1og-0tHrjSXT"
      },
      "source": [
        "### Task: Calculate the Average Numeric Score by Grade Level Using a CASE Statement\n",
        "\n",
        "Write a SQL query to calculate the average numeric score for each `grade_level` using the `CASE` statement. Follow these steps:\n",
        "\n",
        "1. **Select Columns**:\n",
        "   - Retrieve `grade_level` from the `students` table.\n",
        "   - Calculate the average numeric score for grades from the `grades` table and label it as `average_grade`. Use the following numeric mapping for grades:\n",
        "     - 'A' = 4\n",
        "     - 'B' = 3\n",
        "     - 'C' = 2\n",
        "     - 'D' = 1\n",
        "     - Any other grade should be treated as 0.\n",
        "\n",
        "2. **Join Tables**:\n",
        "   - Use an `INNER JOIN` to combine the `students` table with the `grades` table based on the `student_id` column.\n",
        "\n",
        "3. **Group By**:\n",
        "   - Group the results by `grade_level` from the `students` table."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 143
        },
        "id": "2DQ3Zj3emyDN",
        "outputId": "58fd8a36-c276-431f-c7e3-779fb005db6f"
      },
      "outputs": [],
      "source": [
        "gpa_query = \"\"\"\n",
        "YOUR QUERY HERE\n",
        "\"\"\"\n",
        "\n",
        "gpa_result = sql(gpa_query)\n",
        "gpa_result"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "jxMD_z3Ckg9u",
        "outputId": "00877c8b-6a86-482b-f065-6845244c143f"
      },
      "outputs": [],
      "source": [
        "test_instance.test_gpa_query(gpa_result)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Ws3KrK_yuI2a"
      },
      "source": [
        "### Case Expressions:\n",
        "\n",
        "A `CASE` expression is used to evaluate conditions and return values based on those conditions. It works similarly to `if-else` statements in other programming languages.\n",
        "\n",
        "For this exercise, we will create a `CASE` expression using the `students` and `grades` tables. A `CASE` expression begins with `CASE`, followed by the conditions, and ends with an `ELSE` clause for any unmatched cases.\n",
        "\n",
        "In this example, we will add a new column called `grade_check` that categorizes students based on their `numeric_score`. The `CASE` expression will classify the grades as follows:\n",
        "\n",
        "- 'A' for scores of 90 and above\n",
        "- 'B' for scores between 80 and 89\n",
        "- 'C' for scores between 70 and 79\n",
        "- 'F' for scores below 70\n",
        "\n",
        "Your query should include the following columns: `first_name`, `last_name`, `grade`, `numeric_score`, and the newly created `grade_category`.\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 990
        },
        "id": "h8kJNkcEWWpE",
        "outputId": "a4a53e6a-3e55-4bae-d9b3-77f1b0d839b0"
      },
      "outputs": [],
      "source": [
        "case_expression = \"\"\"\n",
        "YOUR QUERY HERE\n",
        "\"\"\"\n",
        "\n",
        "case_result = sql(case_expression)\n",
        "case_result.head(30)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "DbNQC4MWBU5F",
        "outputId": "ce063528-7b48-46b7-cc51-5d461aa23663"
      },
      "outputs": [],
      "source": [
        "test_instance.case_expression_test(case_expression)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "RsAlMLRb5y5g"
      },
      "source": [
        "# exercise 5\n",
        "\n",
        "Subqueries and Advanced Data Management\n",
        "Objective: Explore subqueries and advanced data management techniques.\n",
        "\n",
        "Topics:\n",
        "\n",
        "Subqueries:\n",
        "- Using subqueries in SELECT, WHERE, and FROM clauses.\n",
        "- Correlated subqueries.\n",
        "\n",
        "Views and Indexes:\n",
        "- Creating and using VIEWs.\n",
        "- Understanding and creating indexes.\n",
        "\n",
        "Transactions:\n",
        "- Introduction to transactions and the BEGIN, COMMIT, and ROLLBACK statements.\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "nYZDlbWNzxhM"
      },
      "source": [
        "# Subquery Example\n",
        "\n",
        "A subquery (also called an inner query) is a query nested inside another query. The inner query is executed first, and its result is used by the outer query.\n",
        "\n",
        "Example: Finding the Highest Grade\n",
        "``` sql\n",
        "SELECT student_id, numeric_score\n",
        "FROM grades\n",
        "WHERE numeric_score = (SELECT MAX(numeric_score) FROM grades);\n",
        "Explanation: The subquery (SELECT MAX(numeric_score) FROM grades) finds the highest grade in the grades table. The outer query then retrieves the student_id and numeric_score of the student(s) who received that grade.\n",
        "```\n",
        "\n",
        "Note: The subquery does not depend on the outer query. It runs independently and returns a single value (the maximum grade) that is used in the outer query."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "DQTnUKQK2rmV"
      },
      "source": [
        "# Simple Correlated Subquery Example\n",
        "\n",
        "A correlated subquery depends on the outer query. The inner query is executed once for every row in the outer query, and it references columns from the outer query.\n",
        "\n",
        "Example: Finding Students with Above-Average Scores\n",
        "``` sql\n",
        "SELECT s.student_id, s.first_name, s.last_name\n",
        "FROM students s\n",
        "WHERE s.numeric_score > (\n",
        "    SELECT AVG(g.numeric_score)\n",
        "    FROM grades g\n",
        "    WHERE g.student_id = s.student_id);\n",
        "```\n",
        "\n",
        "Explanation: The outer query retrieves each student's student_id, first_name, and last_name. The correlated subquery (SELECT AVG(g.numeric_score) FROM grades g WHERE g.student_id = s.student_id) calculates the average score for the specific student from the outer query (s.student_id).\n",
        "\n",
        "For each row in the students table, the subquery finds the student's average numeric_score from the grades table, and the outer query checks whether the student’s score is greater than that average.\n",
        "\n",
        "Note: The subquery depends on the outer query because it references s.student_id. It executes once for each student in the outer query."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Gkn3eF3yUU4K"
      },
      "source": [
        "# Creating a VIEW"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "uSD1WqMoxhBp"
      },
      "source": [
        "## GPA `VIEW`\n",
        "\n",
        "### Goal\n",
        "\n",
        "You are tasked with writing a query to calculate the GPA (Grade Point Average) for each student in a school database. The GPA should be based on the average of their `numeric_score` from the `grades` table.\n",
        "\n",
        "The query should display the following information:\n",
        "- Student ID\n",
        "- First Name\n",
        "- Last Name\n",
        "- GPA (average `numeric_score`)\n",
        "\n",
        "### Tables Involved\n",
        "\n",
        "1. `students(student_id, first_name, last_name, grade_level, email)`\n",
        "2. `grades(grade_id, student_id, assignment_id, grade, numeric_score)`\n",
        "\n",
        "Write an SQL query that:\n",
        "- Joins the `students` and `grades` tables using the `student_id`.\n",
        "- Calculates the average `numeric_score` for each student.\n",
        "- Groups the result by `student_id`, so that each student has one row with their GPA.\n",
        "\n",
        "\n",
        "## Key Functions Used in the Query\n",
        "\n",
        "1. **`CREATE VIEW`**: This function creates a virtual table based on a query. The view allows you to save and reuse a query without writing it again. In this case, it creates a view called `StudentGPA`.\n",
        "\n",
        "2. **`SELECT`**: The `SELECT` statement retrieves specific columns of data, including `student_id`, `first_name`, `last_name`, and the calculated GPA (`gpa`).\n",
        "\n",
        "3. **`AVG()`**: The `AVG()` function calculates the average of the `numeric_score` column for each student. This function is used here to calculate the student's GPA based on their grades.\n",
        "\n",
        "4. **`JOIN`**: The `JOIN` function combines rows from the `students` table and the `grades` table based on the `student_id` column. This is an **INNER JOIN**, meaning it only returns students who have matching records in the `grades` table.\n",
        "\n",
        "5. **`GROUP BY`**: This groups the results by `student_id` so that the `AVG()` function calculates the GPA for each student individually.\n",
        "\n",
        "---\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 990
        },
        "id": "sor2sef9UT0k",
        "outputId": "e4ed78a5-50b6-4763-c9b4-699a23fed3a9"
      },
      "outputs": [],
      "source": [
        "gpa_view = \"\"\"\n",
        "YOUR QUERY HERE\n",
        "\"\"\"\n",
        "\n",
        "conn.execute(gpa_view)\n",
        "conn.commit()\n",
        "\n",
        "sql(\"SELECT * FROM StudentGPA\")\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "7sdtOotDC9Qd",
        "outputId": "0ce977d5-baa3-4071-df88-97da263a7565"
      },
      "outputs": [],
      "source": [
        "test_instance.gpa_view_test(conn, gpa_view)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "HkYfDEDCsLAr"
      },
      "source": [
        "## Report card VIEW\n",
        "\n",
        "### Goal\n",
        "\n",
        "You are tasked with creating a query to generate a \"report card\" view for students in a school database. The view should display the following information:\n",
        "\n",
        "- Student ID, first name, and last name.\n",
        "- The numeric scores for the first three assignments as separate columns (`Assignment 1`, `Assignment 2`, `Assignment 3`).\n",
        "- The average participation grade and average behavior grade for each student.\n",
        "\n",
        "### Tables Involved\n",
        "\n",
        "1. `students(student_id, first_name, last_name, grade_level, email)`\n",
        "2. `grades(grade_id, student_id, assignment_id, grade, numeric_score)`\n",
        "3. `assignments(assignment_id, assignment_name, due_date)`\n",
        "4. `participation_behavior(record_id, student_id, attendance, participation_grade, behavior_grade)`\n",
        "\n",
        "Use SQL functions like `CASE`, `MAX()`, `AVG()`, and `LEFT JOIN` to complete the task. Make sure the result includes all students, even if they are missing some grades or participation records.\n",
        "\n",
        "---\n",
        "\n",
        "### Key Functions Used in the Query\n",
        "\n",
        "1. **`CREATE VIEW`**: This function creates a virtual table based on a query. The view allows the same query to be reused without writing it again. In this case, it creates a view called `ReportCard`.\n",
        "\n",
        "2. **`SELECT`**: The `SELECT` statement retrieves columns of data, including `student_id`, `first_name`, and `last_name` from the `students` table.\n",
        "\n",
        "3. **`CASE`**: The `CASE` statement acts like an `IF` statement, allowing conditional logic in SQL. It checks if the `assignment_id` matches a specific value (1, 2, or 3), then returns the `numeric_score` for that assignment.\n",
        "\n",
        "4. **`MAX()`**: The `MAX()` function is used here to pivot data. It selects the highest value (in this case, the single grade) from the `CASE` statements for each `assignment_id`, effectively turning assignments into columns.\n",
        "\n",
        "5. **`AVG()`**: This function calculates the average of values, used here to compute the average participation and behavior grades for each student.\n",
        "\n",
        "6. **`LEFT JOIN`**: The `LEFT JOIN` function ensures all students from the `students` table are included in the results, even if they don’t have matching records in the `grades`, `assignments`, or `participation_behavior` tables.\n",
        "\n",
        "7. **`GROUP BY`**: This groups the result set by `student_id`, `first_name`, and `last_name`, so the aggregate functions (`MAX()`, `AVG()`) apply to each student.\n",
        "\n",
        "---\n",
        "\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 990
        },
        "id": "-wDfKtTirRke",
        "outputId": "dc103ba4-b9d3-4b5f-d998-343c52cc0ce5"
      },
      "outputs": [],
      "source": [
        "report_card_view = \"\"\"\n",
        "YOUR QUERY HERE\n",
        "\"\"\"\n",
        "\n",
        "conn.execute(report_card_view)\n",
        "conn.commit()\n",
        "\n",
        "sql(\"SELECT * FROM ReportCard\")\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "jICfuB34UHv2",
        "outputId": "a62ca5a0-b87b-429b-f7db-d8a54ceb325d"
      },
      "outputs": [],
      "source": [
        "test_instance.report_card_view_test(conn, report_card_view)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "MfEVGQDZgD0A"
      },
      "source": [
        "### What are Views?\n",
        "\n",
        "A `view` is a virtual table in a database, created by writing a query that selects data from one or more underlying tables. Unlike regular tables, views do not store data themselves but instead dynamically display results from the underlying data when queried. You can think of a view as a saved SQL query that can be reused just like a table.\n",
        "\n",
        "When you create a `view`, it’s like taking a snapshot of a specific query, which can then be referenced by name, making it easier to work with complex queries or repeated data needs.\n",
        "\n",
        "Why Use Views?\n",
        "\n",
        "- Simplification of Complex Queries: Views help simplify complex queries by allowing you to store the query logic. Instead of writing a long query repeatedly, you can reference the view, which saves time and reduces errors.\n",
        "\n",
        "  - For example, our report card query is long and uses a a lot of logic. We would use it often so it would be a great use case.\n",
        "\n",
        "- Security: Views can provide an additional layer of security. By restricting access to certain columns or rows in a table, views allow you to expose only the data a user needs to see.\n",
        "\n",
        "  - For example, you might create a view that hides sensitive data, such as emails or personal information, while still allowing access to essential details like grades or attendance.\n",
        "\n",
        "- Consistency: Views ensure that any repetitive business logic or complex calculations are performed consistently every time. This eliminates the risk of inconsistencies if different users write queries differently to access the same information.\n",
        "\n",
        "- Performance: In some cases, views can improve performance by predefining complex joins or calculations. This can make querying faster, especially for commonly used queries."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "BXyZv7Pu4eGz"
      },
      "source": [
        "# Indexes\n",
        "\n",
        "### What is an Index in SQL?\n",
        "An `index` in SQL is like a \"shortcut\" that helps the database find data faster. Instead of scanning the entire table, the database can use the index to quickly locate the rows it needs.\n",
        "\n",
        "### Why Use Indexes?\n",
        "- Faster Searches: If you frequently search for specific data (like finding a student by student_id), an index can speed up the process.\n",
        "- Improved Sorting: When you sort or filter data (e.g., by last_name), an index helps the database do it more efficiently.\n",
        "- Better Performance for Joins: When joining tables (e.g., matching students to their grades), an index can make this process faster.\n",
        "\n",
        "How to Create an Index\n",
        "You can create an index on a column (or multiple columns) in a table to improve performance.\n",
        "\n",
        "**Syntax:**\n",
        "``` sql\n",
        "CREATE INDEX index_name ON table_name (column_name);\n",
        "```\n",
        "Example: Creating an Index on student_id\n",
        "``` sql\n",
        "CREATE INDEX idx_student_id ON students (student_id);\n",
        "```\n",
        "**Explanation:**\n",
        "This creates an `index` on the student_id column of the students table. Now, any query that searches for a student by student_id will be faster.\n",
        "Considerations for Indexes\n",
        "Indexes Speed Up Queries: They are helpful when searching, filtering, or sorting data.\n",
        "Indexes Use Extra Storage: They take up space in the database.\n",
        "Indexes Slow Down Updates: When adding, updating, or deleting data, the index also needs to be updated, which can slow things down slightly.\n",
        "When to Use Indexes\n",
        "Create indexes on columns that are frequently used in WHERE clauses (e.g., student_id).\n",
        "Avoid creating too many indexes because they require extra space and can slow down data changes.\n",
        "Example Query Using an Index:\n",
        "Suppose you have a grades table and frequently search by student_id. Creating an index on student_id makes these searches faster:\n",
        "\n",
        "sql\n",
        "Copy code\n",
        "CREATE INDEX idx_grades_student_id ON grades (student_id);\n",
        "Indexes help make your database more efficient, especially for large datasets. Just remember to use them wisely to avoid performance issues!"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "authorship_tag": "ABX9TyP7CM8lHouuDjHXN3aFsffZ",
      "collapsed_sections": [
        "eX6q2jLxP8Ea",
        "rk8bAu2IQWBZ",
        "UAvB16AFxyKR"
      ],
      "include_colab_link": true,
      "provenance": []
    },
    "kernelspec": {
      "display_name": "Python 3",
      "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": 0
}