Activity 3.5

Source Code

import sqlite3

def main():
    # Connect to the SQLite database
    db = 'activity35.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 games (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        genre TEXT NOT NULL,
        release_year INTEGER,
        publisher TEXT,
        rating REAL,
        platform TEXT
        )
        '''

    # 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 = [
        ('Minecraft', 'Sandbox', 2011, 'Mojang Studios', 4.8, 'Multi-platform'),
        ('Fortnite', 'Battle Royale', 2017, 'Epic Games', 4.5, 'Multi-platform'),
        ('Among Us', 'Party', 2018, 'InnerSloth', 4.7, 'Multi-platform'),
        ('Roblox', 'Game Creation', 2006, 'Roblox Corporation', 4.4, 'Multi-platform'),
        ('Call of Duty: Modern Warfare', 'First-person shooter', 2019, 'Activision', 4.3, 'Multi-platform'),
        ('FIFA 23', 'Sports', 2022, 'EA Sports', 4.1, 'Multi-platform'),
        ('Grand Theft Auto V', 'Action-Adventure', 2013, 'Rockstar Games', 4.9, 'Multi-platform'),
        ('The Legend of Zelda: Breath of the Wild', 'Action-Adventure', 2017, 'Nintendo', 4.9, 'Switch'),
        ('Spider-Man: Miles Morales', 'Action-Adventure', 2020, 'Sony Interactive Entertainment', 4.7, 'PlayStation'),
        ('Halo Infinite', 'First-person shooter', 2021, 'Xbox Game Studios', 4.5, 'Xbox'),
        ('Valorant', 'First-person shooter', 2020, 'Riot Games', 4.6, 'PC'),
        ('Apex Legends', 'Battle Royale', 2019, 'Electronic Arts', 4.4, 'Multi-platform'),
        ('Animal Crossing: New Horizons', 'Life simulation', 2020, 'Nintendo', 4.7, 'Switch'),
        ('Rocket League', 'Sports', 2015, 'Psyonix', 4.6, 'Multi-platform'),
        ('Genshin Impact', 'Action RPG', 2020, 'miHoYo', 4.5, 'Multi-platform')
    ]

    # Insert data into table query
    sql = """
        INSERT INTO games (title, genre, release_year, publisher, rating, platform)
        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 games"

    # 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 all games with titles starting with 'M'
    

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

    # fetch the rows
    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()

    # Games published by companies ending with 'Games'
    
    
    # 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 all games with exactly 7 characters in the title
    

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

    # Games with 'of' in title and released after 2015
    

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