Activity 3.3

Source Code

import sqlite3

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

    done = False
    print("Welcome to the Main Function")
    while not done:
        print("Menu")
        print("E1 - CREATE Example")
        print("E2 - INSERT Example")
        print("E3 - SELECT Example v1")
        print("E4 - SELECT Example v2")
        print("E5 - SELECT Example v3")
        print("E6 - SELECT Example v4")
        print("Q - Quit")
        choice = input("Choice: ").upper()
        match choice:
            case "E1":
                create(conn)
            case "E2":
                insert(conn)
            case "E3":
                select(conn)
            case "E4":
                select2(conn)
            case "E5":
                select3(conn)
            case "E6":
                select4(conn)
            case "Q":
                print("Quitting!")
                done = True
            # default case
            case _:
                print("Invalid, try again!")

    # close the connection
    conn.close()

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 employees (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        department TEXT NOT NULL,
        salary REAL,
        hire_date TEXT,
        is_manager BOOLEAN
        )
        '''

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

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

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

    # data
    data = [
        ('John Smith', 'Engineering', 75000, '2020-05-15', 0),
        ('Jane Doe', 'Marketing', 65000, '2019-03-20', 0),
        ('Michael Johnson', 'Engineering', 85000, '2018-11-10', 1),
        ('Emily Williams', 'Human Resources', 60000, '2021-01-08', 0),
        ('Robert Brown', 'Finance', 90000, '2017-07-22', 1),
        ('Sarah Miller', 'Marketing', 72000, '2020-09-30', 1),
        ('David Garcia', 'Engineering', 78000, '2019-06-12', 0),
        ('Jennifer Martinez', 'Human Resources', 62000, '2021-04-18', 0),
        ('William Thompson', 'Finance', 95000, '2016-10-05', 1),
        ('Lisa Rodriguez', 'Marketing', 68000, '2020-02-28', 0)
    ]

    # Insert data into table query
    sql = """
        INSERT INTO employees (name, department, salary, hire_date, is_manager)
        VALUES (?, ?, ?, ?, ?)
    """

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

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

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 employees"

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

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

def select2(conn):
    print("Select Data")

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

    # SQL Query to SELECT certain columns
    

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

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

def select3(conn):
    print("Select Data")

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

    # SQL Query to SELECT certain columns
    

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

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

def select4(conn):
    print("Select Data")

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

    # SQL Query to SELECT certain columns
    

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

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

main()