Activity 3.8

Source Code

import sqlite3

def main():
    # Connect to the SQLite database
    db = 'activity38.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 jobs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            student_name TEXT,
            job_location TEXT,
            job_title TEXT,
            shift_date DATE,
            hours_worked REAL,
            hourly_rate REAL,
            tips REAL DEFAULT 0,
            taxes_withheld REAL DEFAULT 0
        )
        '''

    # 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 Johnson', 'Main Street Café', 'Barista', '2024-01-05', 4.5, 16.50, 23.50, 12.25),
        ('Alex Johnson', 'Main Street Café', 'Barista', '2024-01-12', 5.0, 16.50, 32.75, 14.50),
        ('Alex Johnson', 'Main Street Café', 'Barista', '2024-01-19', 4.0, 16.50, 21.25, 10.75),
        ('Alex Johnson', 'Main Street Café', 'Barista', '2024-01-26', 5.5, 16.50, 38.50, 17.25),
        ('Alex Johnson', 'Main Street Café', 'Barista', '2024-02-02', 5.0, 17.00, 29.75, 15.50),
        ('Alex Johnson', 'Main Street Café', 'Barista', '2024-02-09', 4.5, 17.00, 26.50, 14.25),
        ('Alex Johnson', 'Main Street Café', 'Shift Supervisor', '2024-02-16', 6.0, 18.00, 31.25, 19.75),
        ('Alex Johnson', 'Main Street Café', 'Shift Supervisor', '2024-02-23', 5.5, 18.00, 28.75, 18.25),
        ('Maya Patel', 'FreshMart Grocery', 'Cashier', '2024-01-06', 5.0, 15.75, 0.00, 13.50),
        ('Maya Patel', 'FreshMart Grocery', 'Cashier', '2024-01-13', 6.0, 15.75, 0.00, 16.25),
        ('Maya Patel', 'FreshMart Grocery', 'Cashier', '2024-01-20', 4.5, 15.75, 0.00, 12.25),
        ('Maya Patel', 'FreshMart Grocery', 'Cashier', '2024-01-27', 5.5, 15.75, 0.00, 15.00),
        ('Maya Patel', 'FreshMart Grocery', 'Cashier', '2024-02-03', 6.5, 16.25, 0.00, 18.25),
        ('Maya Patel', 'FreshMart Grocery', 'Cashier', '2024-02-10', 5.0, 16.25, 0.00, 14.00),
        ('Maya Patel', 'FreshMart Grocery', 'Customer Service', '2024-02-17', 6.0, 16.75, 0.00, 17.25),
        ('Maya Patel', 'FreshMart Grocery', 'Customer Service', '2024-02-24', 5.5, 16.75, 0.00, 15.75),
        ('Jamal Washington', 'Cineplex Theaters', 'Ticket Attendant', '2024-01-07', 4.0, 15.50, 0.00, 10.75),
        ('Jamal Washington', 'Cineplex Theaters', 'Ticket Attendant', '2024-01-14', 5.0, 15.50, 0.00, 13.50),
        ('Jamal Washington', 'Cineplex Theaters', 'Concession Stand', '2024-01-21', 4.5, 15.50, 0.00, 12.00),
        ('Jamal Washington', 'Cineplex Theaters', 'Concession Stand', '2024-01-28', 6.0, 15.50, 0.00, 16.00),
        ('Jamal Washington', 'Bella Italian', 'Server', '2024-02-04', 5.0, 12.50, 75.25, 13.75),
        ('Jamal Washington', 'Bella Italian', 'Server', '2024-02-11', 5.5, 12.50, 90.50, 14.25),
        ('Jamal Washington', 'Bella Italian', 'Server', '2024-02-18', 6.0, 12.50, 95.75, 15.00),
        ('Jamal Washington', 'Bella Italian', 'Server', '2024-02-25', 5.0, 12.50, 82.25, 13.25),
        ('Sophie Rodriguez', 'Urban Styles', 'Sales Associate', '2024-01-08', 5.0, 15.00, 0.00, 12.50),
        ('Sophie Rodriguez', 'Urban Styles', 'Sales Associate', '2024-01-15', 4.5, 15.00, 0.00, 11.50),
        ('Sophie Rodriguez', 'Urban Styles', 'Sales Associate', '2024-01-22', 6.0, 15.00, 0.00, 15.25),
        ('Sophie Rodriguez', 'Urban Styles', 'Sales Associate', '2024-01-29', 5.5, 15.00, 0.00, 14.00),
        ('Sophie Rodriguez', 'Urban Styles', 'Sales Associate', '2024-02-05', 5.0, 15.50, 0.00, 13.25),
        ('Sophie Rodriguez', 'Urban Styles', 'Visual Merchandiser', '2024-02-12', 6.5, 16.00, 0.00, 17.75),
        ('Sophie Rodriguez', 'Urban Styles', 'Visual Merchandiser', '2024-02-19', 5.0, 16.00, 0.00, 13.75),
        ('Sophie Rodriguez', 'Urban Styles', 'Visual Merchandiser', '2024-02-26', 5.5, 16.00, 0.00, 15.00),
        ('Tyler Chen', 'Quick Fix Tech', 'Repair Assistant', '2024-01-09', 4.0, 17.00, 0.00, 11.50),
        ('Tyler Chen', 'Quick Fix Tech', 'Repair Assistant', '2024-01-16', 5.0, 17.00, 0.00, 14.75),
        ('Tyler Chen', 'Quick Fix Tech', 'Repair Assistant', '2024-01-23', 4.5, 17.00, 0.00, 13.25),
        ('Tyler Chen', 'Brain Power Tutoring', 'Math Tutor', '2024-01-30', 3.0, 20.00, 0.00, 10.25),
        ('Tyler Chen', 'Brain Power Tutoring', 'Math Tutor', '2024-02-06', 4.0, 20.00, 0.00, 13.75),
        ('Tyler Chen', 'Brain Power Tutoring', 'Math Tutor', '2024-02-13', 3.5, 20.00, 0.00, 12.00),
        ('Tyler Chen', 'Brain Power Tutoring', 'Math Tutor', '2024-02-20', 4.5, 22.00, 0.00, 16.75),
        ('Tyler Chen', 'Brain Power Tutoring', 'Computer Science Tutor', '2024-02-27', 3.0, 25.00, 0.00, 12.75)
    ]

    # Insert data into table query
    sql = """
        INSERT INTO jobs (student_name, job_location, job_title, shift_date, hours_worked, hourly_rate, tips, taxes_withheld)
        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 jobs"

    # 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 students who have earned more than $500 in total
    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 3 jobs with the highest hourly rates
    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()

    # Top job title that have the highest tips having hours worked >= 5
    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()