Activity 2.17

Source Code

import sqlite3

def create_connection(db_file="activity217.db"):
    """Create a database connection to the SQLite database specified by db_file."""
    connection = None
    try:
        connection = sqlite3.connect(db_file)
        print("Connection to SQLite DB successful")
    except sqlite3.Error as e:
        print(f"Error '{e}' occurred while connecting to database")
    return connection

def create_students_table(connection):
    """Create the Students table if it doesn't exist."""
    cursor = connection.cursor()
    query = """
    CREATE TABLE IF NOT EXISTS Students (
        student_id INTEGER PRIMARY KEY AUTOINCREMENT,
        first_name TEXT NOT NULL,
        last_name  TEXT NOT NULL
    );
    """
    cursor.execute(query)
    connection.commit()
    print("Students table created successfully.")

def create_grades_table(connection):
    """Create the Grades table if it doesn't exist, referencing the Students table."""
    cursor = connection.cursor()
    query = """
    CREATE TABLE IF NOT EXISTS Grades (
        grade_id   INTEGER PRIMARY KEY AUTOINCREMENT,
        student_id INTEGER NOT NULL,
        subject    TEXT NOT NULL,
        grade      TEXT NOT NULL,
        FOREIGN KEY(student_id) REFERENCES Students(student_id)
    );
    """
    cursor.execute(query)
    connection.commit()
    print("Grades table created successfully.")

def insert_student(connection, first_name, last_name):
    """Insert a new student record."""
    cursor = connection.cursor()
    query = """
    INSERT INTO Students (first_name, last_name)
    VALUES (?, ?);
    """
    cursor.execute(query, (first_name, last_name))
    connection.commit()
    print(f"Inserted student: {first_name} {last_name}")

def insert_grade(connection, student_id, subject, grade):
    """Insert a new grade record linked to a student_id."""
    cursor = connection.cursor()
    query = """
    INSERT INTO Grades (student_id, subject, grade)
    VALUES (?, ?, ?);
    """
    cursor.execute(query, (student_id, subject, grade))
    connection.commit()
    print(f"Inserted grade: Student={student_id}, Subject={subject}, Grade={grade}")

def main():
    # 1. Connect to or create the database
    conn = create_connection("activity217.db")

    # 2. Create the necessary tables
    create_students_table(conn)
    create_grades_table(conn)

    # 3. Insert some sample students
    insert_student(conn, "Alice", "Brown")   # student_id = 1 (AUTOINCREMENT)
    insert_student(conn, "Bob", "Smith")     # student_id = 2 (AUTOINCREMENT)
    insert_student(conn, "Charlie", "Davis") # student_id = 3 (AUTOINCREMENT)

    # 4. Insert some grades for the students
    insert_grade(conn, 1, "Math", "A")
    insert_grade(conn, 1, "English", "B+")
    insert_grade(conn, 2, "Math", "B-")
    insert_grade(conn, 3, "History", "A")

    # 5. Fetch and display the joined results from both tables
    cursor = conn.cursor()
    cursor.execute(
        """
        SELECT s.student_id, s.first_name, s.last_name, g.subject, g.grade
        FROM Grades AS g
        INNER JOIN Students AS s
            ON g.student_id = s.student_id
        ORDER BY s.student_id;
        """
    )

    rows = cursor.fetchall()
    print("\n--- Joined Results from Students and Grades ---")
    for row in rows:
        print(row)

    # 6. Close the database connection when done
    conn.close()
    print("\nDatabase connection closed.")

main()

Join Query

SELECT 
    Students.student_id,
    Students.first_name,
    Students.last_name,
    Grades.subject,
    Grades.grade
FROM Students
INNER JOIN Grades
    ON Students.student_id = Grades.student_id;