Question 4
==

You are tasked with creating a report to track students' performance across different modules. The student portal
has the following database tables

Students
- ○ student_id
- ○ first_name
- ○ last_name
- ○ date_of_birth
- ○ grade_level

Subjects
- ○ subject_id
- ○ subject_name

Enrollments
- ○ student_id
- ○ subject_id
- ○ enrollment_date

Grades
- ○ student_id
- ○ subject_id ○ grade
- ○ grade_date

A. Write an SQL query to generate a report that lists all students, their enrolled subjects, and their most recent grade. The report should include
- a. Student's first name, last name, and grade level.
- b. Subject name.
- c. The most recent grade for each student in each subject (if available).

B. What would you do to handle cases where no grade is available for a student in a particular module?

C. Can you write a query that ranks students based on their most recent grade for each module?

### Solution

To follow along:

Git clone the following repository: https://github.com/x0can/LMS-API/tree/main

Run `cd LMS-API`

in db.connection.py 

import sqlite3

def get_connection():
"""Establish a connection to the SQLite database."""
conn = sqlite3.connect('_courses_.db')
conn.row_factory = sqlite3.Row # Access rows as dictionaries
return conn

Create a model to aid in creating the following tables `Students`, `Subjects`, `Enrollments`, `Grades`

Here is the proposed SQL schema

Assumptions: All `*_id` for the tables are integers

CREATE TABLE IF NOT EXISTS Students (
student_id INTEGER PRIMARY KEY AUTOINCREMENT,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
date_of_birth TEXT NOT NULL,
grade_level TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS Subjects (
subject_id INTEGER PRIMARY KEY AUTOINCREMENT,
subject_name TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS Enrollments (
enrollment_id INTEGER PRIMARY KEY AUTOINCREMENT,
student_id INTEGER,
subject_id INTEGER,
enrollment_date TEXT NOT NULL,
FOREIGN KEY(student_id) REFERENCES Students(student_id),
FOREIGN KEY(subject_id) REFERENCES Subjects(subject_id)
);

CREATE TABLE IF NOT EXISTS Grades (
grade_id INTEGER PRIMARY KEY AUTOINCREMENT,
student_id INTEGER,
subject_id INTEGER,
grade TEXT NOT NULL,
grade_date TEXT NOT NULL,
FOREIGN KEY(student_id) REFERENCES Students(student_id),
FOREIGN KEY(subject_id) REFERENCES Subjects(subject_id)
);

Then let's now update  `models.course_sql_operations.py` using the above schema

from db.connection import get_connection

class CourseOperations:
def __init__(self):
self.conn = get_connection()
self.cursor = get_connection().cursor()

def create_tables(self):
"""Create the database tables."""
self.cursor.executescript("""
CREATE TABLE IF NOT EXISTS Students (
student_id INTEGER PRIMARY KEY AUTOINCREMENT,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
date_of_birth TEXT NOT NULL,
grade_level TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS Subjects (
subject_id INTEGER PRIMARY KEY AUTOINCREMENT,
subject_name TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS Enrollments (
enrollment_id INTEGER PRIMARY KEY AUTOINCREMENT,
student_id INTEGER,
subject_id INTEGER,
enrollment_date TEXT NOT NULL,
FOREIGN KEY(student_id) REFERENCES Students(student_id),
FOREIGN KEY(subject_id) REFERENCES Subjects(subject_id)
);

CREATE TABLE IF NOT EXISTS Grades (
grade_id INTEGER PRIMARY KEY AUTOINCREMENT,
student_id INTEGER,
subject_id INTEGER,
grade TEXT NOT NULL,
grade_date TEXT NOT NULL,
FOREIGN KEY(student_id) REFERENCES Students(student_id),
FOREIGN KEY(subject_id) REFERENCES Subjects(subject_id)
);
""")
self.conn.commit()

def clean_up(self):
"""Delete all existing data from the database."""
self.cursor.executescript("""
DELETE FROM Grades;
DELETE FROM Enrollments;
DELETE FROM Subjects;
DELETE FROM Students;
""")
self.conn.commit()

def close(self):
"""Close the database connection."""
self.cursor.close()
self.conn.close()

To test this if it works, we will create a faker named `course_operations_faker.py`, inside `/db` 

db
...
course_operations_faker.py
...

from faker import Faker
import random
from db.connection import get_connection

fake = Faker()

def create_sample_students(num_students=10):
conn = get_connection()
cursor = conn.cursor()
try:
for _ in range(num_students):
first_name = fake.first_name()
last_name = fake.last_name()
date_of_birth = fake.date_of_birth(minimum_age=14, maximum_age=18).strftime('%Y-%m-%d')
grade_level = fake.random_int(min=9, max=12)

cursor.execute("""
INSERT INTO Students (first_name, last_name, date_of_birth, grade_level)
VALUES (?, ?, ?, ?)
""", (first_name, last_name, date_of_birth, grade_level))
conn.commit()
finally:
cursor.close()
conn.close()

def create_sample_subjects():
conn = get_connection()
cursor = conn.cursor()
try:
subjects = ['Math', 'Science', 'English', 'History', 'Geography']
for subject in subjects:
cursor.execute("""
INSERT INTO Subjects (subject_name)
VALUES (?)
""", (subject,))
conn.commit()
finally:
cursor.close()
conn.close()

def create_sample_enrollments(num_students=10, num_subjects=5):
conn = get_connection()
cursor = conn.cursor()
try:
for student_id in range(1, num_students + 1):
for subject_id in range(1, num_subjects + 1):
cursor.execute("""
INSERT INTO Enrollments (student_id, subject_id, enrollment_date)
VALUES (?, ?, ?)
""", (student_id, subject_id, fake.date_this_decade().strftime('%Y-%m-%d')))
conn.commit()
finally:
cursor.close()
conn.close()

def create_sample_grades(num_students=10, num_subjects=5):
conn = get_connection()
cursor = conn.cursor()
try:
for student_id in range(1, num_students + 1):
for subject_id in range(1, num_subjects + 1):
grade = random.choice(['A', 'B', 'C', 'D'])
cursor.execute("""
INSERT INTO Grades (student_id, subject_id, grade, grade_date)
VALUES (?, ?, ?, ?)
""", (student_id, subject_id, grade, fake.date_this_decade().strftime('%Y-%m-%d')))
conn.commit()
finally:
cursor.close()
conn.close()

finally, we can connect our new logic to  `main.py`

from db import (
create_sample_enrollments, create_sample_grades, create_sample_students, create_sample_subjects)
from models.course_sql_operations import CourseOperations
from flask import Flask
from routes import configure_routes
from config import Config
from db.student_queries import get_student_performance

# Initialize Flask app
app = Flask(__name__)
app.config.from_object(Config)

# Configure routes
configure_routes(app)

if __name__ == "__main__":
course_operations = CourseOperations()
course_operations.create_tables()
# course_operations.clean_up()

print("Populating database with sample data...")

create_sample_students(10)
create_sample_subjects()
create_sample_enrollments(10, 5)
create_sample_grades(10, 5)

print("Database with sample data created")

course_operations.close()
app.run(debug=True)

Run `python3 main.py`

If you see the following statement on the terminal

`Database with sample data created`

Everything worked 🎊





Next we need to create an SQL Query that will handle some key functionalities listed below [here](#features)

After which we will connect it to an endpoint to test if it works


Inside `db` folder, let's add a query handler for `SQL`.  

### SQL Query
student_queries.py

def get_student_performance():
query = """
SELECT
s.first_name,
s.last_name,
s.grade_level,
sub.subject_name,
g.grade,
CASE
WHEN g.grade = 'A' THEN 1
WHEN g.grade = 'B' THEN 2
WHEN g.grade = 'C' THEN 3
WHEN g.grade = 'D' THEN 4
ELSE 5
END AS grade_rank
FROM
Students s
JOIN
Enrollments e ON s.student_id = e.student_id
JOIN
Subjects sub ON e.subject_id = sub.subject_id
LEFT JOIN
Grades g ON s.student_id = g.student_id AND sub.subject_id = g.subject_id
AND g.grade_date = (
SELECT MAX(grade_date)
FROM Grades
WHERE student_id = s.student_id
AND subject_id = sub.subject_id
)
ORDER BY
grade_rank, s.student_id, sub.subject_name;

"""
return query

### features

### 1. Handle cases where no grade is available for a student in a particular module

- The query uses a `LEFT JOIN` between Grades and the other tables, to ensure that students who do not have grades for specific subjects will still appear in the result with `NULL` for the grade.
- If a student has no grade in a subject, their grade column will contain `NULL`, and the ranking logic (based on grade) will be skipped.


### 2. Rank students based on their most recent grade for each module

- It uses `CASE` statement to map grades to numerical ranks ('A' = 1, 'B' = 2, 'C' = 3, 'D' = 4), which allows the students to be ranked based on their most recent grade.
- The query orders the results by `grade_rank`, so students with the best grades (e.g., A = 1) will be ranked higher.
- Use `ORDER BY` to ensure that the students are sorted by their grade rank first, then by their student ID and subject name.


Now in main.py, let's create a simple endpoint to test this logic

# ... Previous Logic

from db.connection import get_connection

@app.route("/api/v1/report", methods=["GET"])
def fetch_report():
"""Fetch and display the student performance report."""
conn = get_connection()
cursor = conn.cursor()
try:
query = get_student_performance()
cursor.execute(query)
results = cursor.fetchall()
report = [
{
"first_name": row["first_name"],
"last_name": row["last_name"],
"grade_level": row["grade_level"],
"subject_name": row["subject_name"],
"grade": row["grade"],
}
for row in results
]
return {"data": report}, 200
finally:
cursor.close()
conn.close()

if __name__ == "__main__":
#...Previous Logic

Run `Python3 main.py` incase your previous session closed.


Then navigate to 

GET http://localhost:5000/api/v1/report

You should see a json response

Here is a snapshot of how it should look

{
"data": [
{
"first_name": "Derek",
"grade": "A",
"grade_level": "11",
"last_name": "Thomas",
"subject_name": "History"
},
{
"first_name": "Derek",
"grade": "A",
"grade_level": "11",
"last_name": "Thomas",
"subject_name": "English"
},
{
"first_name": "Andrea",
"grade": "A",
"grade_level": "11",
"last_name": "Fox",
"subject_name": "English"
},

.......