Activity 3.11

Source Code

import sqlite3

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

    done = False
    print("Welcome to the Main Function")
    while not done:
        print("Menu")
        print("CREATE1 - CREATE TABLE 1")
        print("CREATE2 - CREATE TABLE 2")
        print("CREATE3 - CREATE TABLE 3")
        print("INSERT1 - INSERT INTO TABLE 1")
        print("INSERT2 - INSERT INTO TABLE 2")
        print("INSERT3 - INSERT INTO TABLE 3")
        print("SELECT1 - SELECT * TABLE 1")
        print("SELECT2 - SELECT * TABLE 2")
        print("SELECT3 - SELECT * TABLE 3")
        print("E1 - Example 1")
        print("Q - Quit")
        choice = input("Choice: ").upper()
        match choice:
            case "CREATE1":
                create1(conn)
            case "CREATE2":
                create2(conn)
            case "INSERT1":
                insert1(conn)
            case "INSERT2":
                insert2(conn)
            case "SELECT1":
                select1(conn)
            case "SELECT2":
                select2(conn)
            case "SELECT3":
                select3(conn)
            case "CREATE3":
                create3(conn)
            case "INSERT3":
                insert3(conn)
            case "E1":
                example1(conn)
            case "E2":
                example2(conn)
            case "Q":
                done = True
            # default case
            case _:
                print("Invalid, try again!")

    # close the connection
    conn.close()

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

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

    # Create table query
    sql = '''CREATE TABLE IF NOT EXISTS clubs (
            club_id INTEGER PRIMARY KEY AUTOINCREMENT,
            club_name TEXT NOT NULL,
            meeting_day TEXT,
            room_number TEXT
            )
           '''

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

# create function
def create2(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 (
        student_id INTEGER PRIMARY KEY AUTOINCREMENT,
        first_name TEXT NOT NULL,
        grade INTEGER,
        club_id INTEGER,
        gpa REAL,
        FOREIGN KEY (club_id) REFERENCES clubs (club_id)
        )
        '''

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

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

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

    # data
    data = [
        ('eSports Club', 'Monday', 'D202'),
        ('Debate Team', 'Tuesday', 'A205'),
        ('Basketball', 'Wednesday', 'Main Gym'),
        ('Art Club', 'Thursday', 'C202'),
        ('Robotics', 'Friday', 'STEM OLA')
    ]

    # Insert data into table query
    sql = """
        INSERT INTO clubs (club_name, meeting_day, room_number)
        VALUES (?, ?, ?)
    """

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

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

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

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

    # data
    data = [
        ('Alex', 10, 1, 3.7),
        ('Alex', 10, 2, 3.7),
        ('Taylor', 11, 2, 3.9),
        ('Taylor', 11, 4, 3.9),
        ('Jordan', 10, 1, 3.5),
        ('Casey', 12, 3, 3.8),
        ('Riley', 11, 2, 3.6),
        ('Riley', 11, 5, 3.6),
        ('Morgan', 10, 1, 3.4),
        ('Jamie', 12, None, 3.1), # No club
        ('Avery', 9, 6, 3.2) # Club doesn't exist
    ]

    # Insert data into table query
    sql = """
        INSERT INTO students (first_name, grade, club_id, gpa)
        VALUES (?, ?, ?, ?)
    """

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

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

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

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

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

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

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

# select function
def select2(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)

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

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

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

    # 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()

    # sql
    sql = '''
            
            '''
    # Execute query
    cursor.execute(sql)
    rows = cursor.fetchall()
    for row in rows:
        print(row)

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

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

    # Create table query
    sql = '''CREATE TABLE IF NOT EXISTS competitions (
            comp_id INTEGER PRIMARY KEY AUTOINCREMENT,
            comp_name TEXT NOT NULL,
            club_id INTEGER,
            location TEXT,
            comp_date TEXT,
            FOREIGN KEY (club_id) REFERENCES clubs (club_id)
            )
    '''

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

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

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

    # data
    data = [
        ('State Gaming Tournament', 1, 'Convention Center', '2023-11-15'),
        ('Regional Debate Championship', 2, 'Central High School', '2023-10-25'),
        ('Basketball Finals', 3, 'Main Gym', '2023-12-10'),
        ('Robotics Challenge', 5, 'Tech University', '2023-11-05'),
        ('Art Exhibition', 4, 'Community Center', '2023-10-30')
    ]

    # Insert data into table query
    sql = """
        INSERT INTO competitions (comp_name, club_id, location, comp_date)
        VALUES (?, ?, ?, ?)
    """

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

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

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

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

    # sql
    sql = '''SELECT s.first_name, c.club_name, comp.comp_name, comp.comp_date
            FROM students s
            INNER JOIN clubs c ON s.club_id = c.club_id
            INNER JOIN competitions comp ON c.club_id = comp.club_id
    '''

    # Execute query
    cursor.execute(sql)
    rows = cursor.fetchall()
    for row in rows:
        print(row)

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