Source Code

session9.py

import sqlite3
import os

def main():
    # Connect to the SQLite database
    db = 'session9.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")
        print("E4 - DELETE Example")
        print("Q - Quit")
        choice = input("Choice: ").upper()
        if choice == "E1":
            create(conn)
        elif choice == "E2":
            insert(conn)
        elif choice == "E3":
            select(conn)
        elif choice == "E4":
            delete(conn)
        elif choice == "E5":
            example5(conn)
        elif choice == "E6":
            example6(conn)
        elif choice == "E7":
            example7(conn)
        elif choice == "E8":
            example8(conn)
        elif choice == "E9":
            example9(conn)
        elif choice == "E10":
            example10(conn)
        elif choice == "E11":
            example11(conn)
        elif choice == "E12":
            example12(conn)
        elif choice == "E13":
            example13(conn)
        elif choice == "E14":
            example14(conn)
        elif choice == "E15":
            example15(conn)
        elif choice == "E16":
            example16(conn)
        elif choice == "E17":
            example17(conn)
        elif choice == "E18":
            example18(conn)
        elif choice == "E19":
            example19(conn)
        elif choice == "E20":
            example20(conn)
        elif choice == "E21":
            example21(conn)
        elif choice == "E22":
            example22(conn)
        elif choice == "Q":
            print("Quitting!")
            done = True
        else:
            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 = ''

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

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

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

    # Insert data into table query
    sql = ""

    # Execute query
    cursor.execute(sql)
    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 students
    sql = ""

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

    # fetch the rows


def delete(conn):
    print("Delete")

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

    # SQL Query to DELETE all students
    sql = ""

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

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

def example5(conn):
    print("Create Table")

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

    # Create table query
    sql = '''

        '''

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

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

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

    # student data
    

    # Insert data into table query
    sql = """
    
    """

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

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

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

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

    # SQL Query to SELECT all learners
    sql = ""

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

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

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

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

    # SQL Query to SELECT certain columns from learners
    sql = ""


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

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

def example9(conn):
    print("Delete")

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

    # SQL Query to DELETE all students
    sql = "DELETE FROM learners"

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

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

def example10(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 example11(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 example12(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 example13(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 example14(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 example15(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)

# create function
def example16(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 example17(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 example18(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)

def example19(conn):
    # 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 example20(conn):
    # Create a cursor object using the cursor() method
    cursor = conn.cursor()

    # Find games in the FPS genre with price over $50

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

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

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

    # Find games that are either free or in the Sports genre

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

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

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

    # Find games that don't support multiplayer

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

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

main()