Activity 3.7

Source Code

import sqlite3

def main():
    # Connect to the SQLite database
    db = 'activity37.db'
    conn = sqlite3.connect(db)

    done = False
    print("Welcome to the Main Function")
    while not done:
        print("Menu")
        print("CREATE - CREATE TABLE")
        print("INSERT - INSERT INTO TABLE")
        print("SELECT - SELECT *")
        print("E1 - Example 1")
        print("Q - Quit")
        choice = input("Choice: ").upper()
        match choice:
            case "CREATE":
                create(conn)
            case "INSERT":
                insert(conn)
            case "SELECT":
                select(conn)
            case "E1":
                example1(conn)
            case "E2":
                example2(conn)
            case "E3":
                example3(conn)
            case "E4":
                example4(conn)
            case "P1":
                problem1(conn)
            case "P2":
                problem2(conn)
            case "P3":
                problem3(conn)
            case "P4":
                problem4(conn)
            case "P5":
                problem5(conn)
            case "P6":
                problem6(conn)
            case "P7":
                problem7(conn)
            case "P8":
                problem8(conn)
            case "P9":
                problem9(conn)
            case "P10":
                problem10(conn)
            case "Q":
                print("Quitting!")
                done = True
            # default case
            case _:
                print("Invalid, try again!")

    # close the connection
    conn.close()

# create function
def create(conn):
    print("Create Table")

    # Create a cursor object using the cursor() method
    cursor = conn.cursor()

    # Create table query
    sql = '''
        CREATE TABLE IF NOT EXISTS students (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            student_name TEXT NOT NULL,
            course TEXT NOT NULL,
            grade INTEGER NOT NULL,
            school_year TEXT NOT NULL
        )
        '''

    # Execute query
    cursor.execute(sql)
    print("Table created successfully!")

# insert function
def insert(conn):
    print("Insert Data")

    # Create a cursor object using the cursor() method
    cursor = conn.cursor()

    # data
    data = [
        ('Alex', 'Algebra 1', 92, '2024-2025'),
        ('Alex', 'English 1', 88, '2024-2025'),
        ('Alex', 'Computer Science 1', 95, '2024-2025'),
        ('Alex', 'US History', 78, '2024-2025'),
        ('Alex', 'Chinese 1', 99, '2024-2025'),
        ('Taylor', 'Algebra 1', 75, '2024-2025'),
        ('Taylor', 'English 1', 90, '2024-2025'),
        ('Taylor', 'Computer Science 1', 82, '2024-2025'),
        ('Taylor', 'US History', 85, '2024-2025'),
        ('Taylor', 'Arabic', 95, '2024-2025'),
        ('Jordan', 'Algebra 1', 88, '2024-2025'),
        ('Jordan', 'English 1', 79, '2024-2025'),
        ('Jordan', 'Computer Science 1', 94, '2024-2025'),
        ('Jordan', 'US History', 81, '2024-2025'),
        ('Jordan', 'Spanish 1', 100, '2024-2025'),
        ('Morgan', 'Algebra 1', 95, '2024-2025'),
        ('Morgan', 'English 1', 87, '2024-2025'),
        ('Morgan', 'Computer Science 1', 91, '2024-2025'),
        ('Morgan', 'US History', 83, '2024-2025'),
        ('Morgan', 'French 1', 79, '2024-2025'),
        ('Casey', 'Algebra 1', 79, '2024-2025'),
        ('Casey', 'English 1', 93, '2024-2025'),
        ('Casey', 'Computer Science 1', 84, '2024-2025'),
        ('Casey', 'US History', 89, '2024-2025'),
        ('Casey', 'Japanese 1', 87, '2024-2025')
        ]

    # Insert data into table query
    sql = """
        INSERT INTO students (student_name, course, grade, school_year)
        VALUES (?, ?, ?, ?)
    """

    # Insert data into table query
    cursor.executemany(sql, data)
    print("SQL query executed")

    # Commit changes
    conn.commit()
    print("changed committed")

# select function
def select(conn):
    print("Select Data")

    # Create a cursor object using the cursor() method
    cursor = conn.cursor()

    # SQL Query to SELECT all
    sql = "SELECT * FROM students"

    # Execute query
    cursor.execute(sql)
    print("SQL query executed")

    # fetch the rows
    rows = cursor.fetchall()
    for row in rows:
        print(row)

# Example 1 Function
def example1(conn):
    print("Example 1")

    # Create a cursor object using the cursor() method
    cursor = conn.cursor()

    # Find the average score for each student
    sql = ""

    # Execute query
    cursor.execute(sql)
    print("SQL query executed")

    # fetch the row
    rows = cursor.fetchall()
    for row in rows:
        print(row)

# Example 2 Function
def example2(conn):
    print("Example 2")

    # Create a cursor object using the cursor() method
    cursor = conn.cursor()

    # Find the max grade for each student
    sql = ""

    # Execute query
    cursor.execute(sql)
    print("SQL query executed")

    # fetch the rows
    rows = cursor.fetchall()
    for row in rows:
        print(row)

# Example 3 Function
def example3(conn):
    print("Example 3")

    # Create a cursor object using the cursor() method
    cursor = conn.cursor()

    # Find the overall class average, minimum and maximum grades
    sql = ""

    # Execute query
    cursor.execute(sql)
    print("SQL query executed")

    # fetch the rows
    rows = cursor.fetchall()
    for row in rows:
        print(row)



# Example 4 Function
def example4(conn):
    print("Example 4")

    # Create a cursor object using the cursor() method
    cursor = conn.cursor()

    # Print each students average grade from greatest to least
    sql = ""

    # Execute query
    cursor.execute(sql)
    print("SQL query executed")

    # fetch the rows
    rows = cursor.fetchall()
    for row in rows:
        print(row)

# Problem 1 Function
def problem1(conn):
    print("Problem 1")

# Problem 2 Function
def problem2(conn):
    print("Problem 2")

# Problem 3 Function
def problem3(conn):
    print("Problem 3")

# Problem 4 Function
def problem4(conn):
    print("Problem 4")

# Problem 5 Function
def problem5(conn):
    print("Problem 5")

# Problem 6 Function
def problem6(conn):
    print("Problem 6")

# Problem 7 Function
def problem7(conn):
    print("Problem 7")

# Problem 8 Function
def problem8(conn):
    print("Problem 8")

# Problem 9 Function
def problem9(conn):
    print("Problem 9")

# Problem 10 Function
def problem10(conn):
    print("Problem 10")

# call to main function, do not delete!
main()