Activity 2.22

Source Code

import sqlite3

def create_and_populate_orders_db(db_name="activity222.db"):
    # 1. Connect to SQLite (creates 'orders.db' if it doesn't exist)
    conn = sqlite3.connect(db_name)
    cursor = conn.cursor()

    # 2. Create the orders table (order_amount is treated as a cost here)
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS orders (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            order_amount REAL NOT NULL
        );
    """)

    # 3. Define a list of 20 sample computer items (name, cost in dollars)
    sample_data = [
        ("Dell XPS 13", 1099.99),
        ("Dell Inspiron 15", 699.99),
        ("HP Spectre x360", 1299.00),
        ("HP Pavilion 14", 549.99),
        ("Lenovo ThinkPad X1 Carbon", 1799.00),
        ("Lenovo IdeaPad 5", 649.99),
        ("Asus ZenBook 14", 999.95),
        ("Asus ROG Strix G15", 1499.00),
        ("Acer Swift 3", 729.99),
        ("Acer Nitro 5", 899.00),
        ("Apple MacBook Air M1", 999.00),
        ("Apple MacBook Pro 14", 1999.00),
        ("Microsoft Surface Laptop 4", 1299.00),
        ("Microsoft Surface Book 3", 1599.99),
        ("Samsung Galaxy Book Pro", 1149.00),
        ("Razer Blade 15", 1699.99),
        ("MSI GF63 Thin", 749.99),
        ("MSI Prestige 14", 1249.99),
        ("Alienware m15 R6", 1799.99),
        ("Google Pixelbook Go", 649.99)
    ]

    # 4. Insert these items into the orders table
    insert_query = "INSERT INTO orders (name, order_amount) VALUES (?, ?)"
    cursor.executemany(insert_query, sample_data)

    # 5. Commit the changes
    conn.commit()

    # 6. (Optional) Retrieve and print the inserted rows
    cursor.execute("SELECT id, name, order_amount FROM orders")
    rows = cursor.fetchall()
    for row in rows:
        print(row)

    # 7. Close the connection
    conn.close()

def create_and_populate_student_grades_db(db_name="activity222.db"):
    # 1. Connect to (or create) the SQLite database
    conn = sqlite3.connect(db_name)
    cursor = conn.cursor()

    # 2. Create the student_grades table if it doesn't exist
    #    We'll store grades as INTEGER this time.
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS student_grades (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            student_name TEXT NOT NULL,
            subject TEXT NOT NULL,
            grade INTEGER NOT NULL
        );
    """)

    # 3. Define 20 sample rows (student_name, subject, grade)
    #    All first names, numeric grades out of 100.
    sample_data = [
        ("John", "Mathematics", 95),
        ("Jane", "English", 82),
        ("Peter", "Science", 76),
        ("Emily", "History", 88),
        ("Michael", "Art", 93),
        ("Sarah", "Mathematics", 79),
        ("David", "Computer Science", 91),
        ("Linda", "Mathematics", 67),
        ("Daniel", "English", 85),
        ("Laura", "Art", 90),
        ("Robert", "Physics", 94),
        ("Patricia", "Chemistry", 70),
        ("Susan", "English", 73),
        ("Thomas", "History", 88),
        ("Karen", "Mathematics", 80),
        ("Mark", "Biology", 97),
        ("Donald", "Computer Science", 86),
        ("Maria", "English", 92),
        ("Paul", "Mathematics", 89),
        ("Jennifer", "Science", 84)
    ]

    # 4. Insert the sample data into the table
    insert_query = """
        INSERT INTO student_grades (student_name, subject, grade)
        VALUES (?, ?, ?)
    """
    cursor.executemany(insert_query, sample_data)

    # 5. Commit the changes
    conn.commit()

    # 7. Close the connection
    conn.close()

if __name__ == "__main__":
    create_and_populate_orders_db()
    create_and_populate_student_grades_db()

Query 1

SELECT COUNT(*) AS total_rows FROM orders;

Query 2

SELECT SUM(order_amount) FROM orders;

Query 3

SELECT AVG(order_amount) AS average_amount FROM orders;

Query 4

SELECT MIN(order_amount) AS average_amount FROM orders;

Query 5

SELECT MAX(order_amount) AS largest_amount FROM orders;