Activity 2.23

Source Code

import sqlite3

def main():
    # 1. Connect to (or create) the SQLite database
    conn = sqlite3.connect("activity223.db")

    # 2. Create a cursor object to execute SQL commands
    cursor = conn.cursor()

    # 3. Create the "employees" table if it does not exist
    create_table_query = """
    CREATE TABLE IF NOT EXISTS employees (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        first_name TEXT NOT NULL,
        last_name TEXT NOT NULL,
        department TEXT NOT NULL,
        salary REAL NOT NULL
    );
    """
    cursor.execute(create_table_query)

    # 4. Prepare the data to insert
    employees_data = [
        ('Alice', 'Johnson', 'Engineering', 75000),
        ('Bob', 'Smith', 'Marketing', 55000),
        ('Charlie', 'Brown', 'Engineering', 68000),
        ('Diana', 'Adams', 'HR', 52000),
        ('Edward', 'Green', 'Marketing', 60000),
        ('Fiona', 'Walker', 'Engineering', 80000),
        ('George', 'Harris', 'Engineering', 70000),
        ('Hannah', 'Clark', 'Finance', 65000),
        ('Ian', 'Lee', 'HR', 50000),
        ('Jasmine', 'Wong', 'Finance', 77000)
    ]

    # 5. Insert the data into the "employees" table
    insert_query = """
    INSERT INTO employees (first_name, last_name, department, salary)
    VALUES (?, ?, ?, ?);
    """

    # Use 'executemany' to insert multiple records at once
    cursor.executemany(insert_query, employees_data)

    # 6. Commit the transaction to persist changes
    conn.commit()

    # 7. (Optional) Retrieve and print the inserted rows to confirm
    select_query = "SELECT * FROM employees;"
    cursor.execute(select_query)
    rows = cursor.fetchall()

    print("Inserted rows in the 'employees' table:")
    for row in rows:
        print(row)

    # 8. Close the connection
    conn.close()

def main2():
    # 1. Connect to (or create) the SQLite database
    #    The database file will be named "school.db".
    conn = sqlite3.connect("activity233.db")

    # 2. Create a cursor object to execute SQL commands
    cursor = conn.cursor()

    # 3. Create the "students" table if it does not exist
    #    We include columns that are typical for high school student data.
    create_table_query = """
    CREATE TABLE IF NOT EXISTS students (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        first_name TEXT NOT NULL,
        last_name TEXT NOT NULL,
        grade_level INTEGER NOT NULL,
        gpa REAL NOT NULL,
        favorite_subject TEXT
    );
    """
    cursor.execute(create_table_query)

    # 4. Prepare a list of 10 sample records for insertion
    #    These rows reflect a mix of grade levels, GPAs, and subjects.
    students_data = [
        ("John", "Smith", 9, 3.2, "Math"),
        ("Emily", "Davis", 11, 3.9, "English"),
        ("Michael", "Johnson", 10, 2.8, "History"),
        ("Sarah", "Lee", 12, 3.7, "Biology"),
        ("Thomas", "Garcia", 9, 3.5, "Physical Education"),
        ("Olivia", "Miller", 12, 4.0, "Chemistry"),
        ("Daniel", "Wilson", 11, 2.9, "Geography"),
        ("Sophia", "Brown", 10, 3.6, "English"),
        ("Liam", "Taylor", 9, 3.0, "Art"),
        ("Ava", "Martinez", 10, 3.4, "Music")
    ]

    # 5. Insert the data into the "students" table
    insert_query = """
    INSERT INTO students (first_name, last_name, grade_level, gpa, favorite_subject)
    VALUES (?, ?, ?, ?, ?);
    """

    # Use 'executemany' to insert multiple records at once
    cursor.executemany(insert_query, students_data)

    # 6. Commit the transaction to save changes
    conn.commit()

    # 7. (Optional) Retrieve and print the inserted rows to confirm
    cursor.execute("SELECT * FROM students;")
    rows = cursor.fetchall()

    print("Inserted rows in the 'students' table:")
    for row in rows:
        print(row)

    # 8. Close the connection
    conn.close()

if __name__ == "__main__":
    main()
    main2()

Query 1

SELECT * FROM employees LIMIT 2;

Query 2

SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

Query 3

SELECT first_name, last_name, salary
FROM employees
WHERE department = 'HR'
ORDER BY salary ASC
LIMIT 2;