Activity 3.4

Source Code

import sqlite3

def main():
    # Connect to the SQLite database
    db = 'activity34.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,
        price REAL NOT NULL,
        release_date TEXT NOT NULL,
        is_multiplayer BOOLEAN 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 = [
        ('Minecraft', 'Sandbox', 29.99, '2011-11-18', 1),
        ('Fortnite', 'Battle Royale', 0.00, '2017-07-25', 1),
        ('The Legend of Zelda', 'Adventure', 59.99, '2023-05-12', 0),
        ('Among Us', 'Party', 4.99, '2018-06-15', 1),
        ('FIFA 24', 'Sports', 69.99, '2023-09-29', 1),
        ('Call of Duty', 'FPS', 59.99, '2022-10-28', 1),
        ('Roblox', 'Platform', 0.00, '2006-09-01', 1),
        ('Spider-Man 2', 'Action', 69.99, '2023-10-20', 0),
        ('Skyrim', 'RPG', 19.99, '2011-11-11', 0),
        ('Rocket League', 'Sports', 19.99, '2015-07-07', 1)
    ]

    # Insert data into table query
    sql = """
        INSERT INTO games (title, genre, price, release_date, is_multiplayer)
        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()

    # Basic WHERE clause to find games in the Adventure genre
    sql = "SELECT * FROM games WHERE genre = 'Adventure'"

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

    # Find games in the FPS genre with price over $50
    sql = "SELECT * FROM games WHERE genre = 'FPS' AND price > 50"

    # 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 games that are either free or in the Sports genre
    sql = "SELECT * FROM games WHERE price = 0 OR genre = 'Sports'"

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

    # Find games that don't support multiplayer
    sql = "SELECT * FROM games WHERE NOT is_multiplayer"

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