Activity 2.19

Source Code

import sqlite3

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

    # 2. Create the college_majors table if it does not exist
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS college_majors (
            major_id INTEGER PRIMARY KEY,
            major_name TEXT NOT NULL,
            early_career_pay INTEGER,
            mid_career_pay INTEGER
        );
    """)

    # 3. Prepare data for 50 college majors
    #    (Values here are approximate, illustrative examples)
    majors_data = [
        (1,  "Accounting",                55000, 92000),
        (2,  "Aerospace Engineering",     68000, 113000),
        (3,  "Agricultural Engineering",  54000, 89000),
        (4,  "Anthropology",             44000, 76000),
        (5,  "Architecture",             51000, 93000),
        (6,  "Biochemistry",             52000, 95000),
        (7,  "Biology",                  46000, 85000),
        (8,  "Biomedical Engineering",   62000, 105000),
        (9,  "Business Administration",  53000, 93000),
        (10, "Chemical Engineering",     69000, 115000),
        (11, "Chemistry",                50000, 90000),
        (12, "Civil Engineering",        60000, 99000),
        (13, "Communications",           43000, 80000),
        (14, "Computer Engineering",     70000, 116000),
        (15, "Computer Science",         75000, 120000),
        (16, "Construction Management",  58000, 95000),
        (17, "Criminal Justice",         42000, 78000),
        (18, "Economics",                63000, 114000),
        (19, "Electrical Engineering",   72000, 120000),
        (20, "English",                  42000, 78000),
        (21, "Environmental Science",    48000, 88000),
        (22, "Finance",                  59000, 115000),
        (23, "Geology",                  51000, 92000),
        (24, "Graphic Design",           45000, 80000),
        (25, "Health Information Management", 48000, 86000),
        (26, "History",                  43000, 77000),
        (27, "Hospitality Management",   44000, 82000),
        (28, "Human Resources Management", 50000, 90000),
        (29, "Industrial Engineering",   63000, 110000),
        (30, "Information Systems",      65000, 108000),
        (31, "Information Technology",   68000, 112000),
        (32, "International Relations",  47000, 88000),
        (33, "Journalism",               41000, 75000),
        (34, "Management",               54000, 100000),
        (35, "Marketing",                50000, 95000),
        (36, "Mathematics",              57000, 110000),
        (37, "Mechanical Engineering",   65000, 110000),
        (38, "Music",                    40000, 72000),
        (39, "Nursing",                  61000, 98000),
        (40, "Philosophy",               42000, 84000),
        (41, "Physics",                  60000, 115000),
        (42, "Political Science",        45000, 87000),
        (43, "Psychology",               42000, 80000),
        (44, "Public Administration",    45000, 85000),
        (45, "Public Health",            48000, 90000),
        (46, "Sociology",                43000, 79000),
        (47, "Software Engineering",     76000, 125000),
        (48, "Statistics",               60000, 110000),
        (49, "Supply Chain Management",  55000, 95000),
        (50, "Urban Planning",           48000, 88000)
    ]

    # 4. Insert the data into the college_majors table
    #    (Use executemany for bulk insertion)
    cursor.executemany("""
        INSERT INTO college_majors (major_id, major_name, early_career_pay, mid_career_pay)
        VALUES (?, ?, ?, ?);
    """, majors_data)

    # 5. Commit the transaction and close the connection
    conn.commit()
    conn.close()
    print("Successfully created and populated the 'activity219' table.")

if __name__ == "__main__":
    create_and_populate_college_majors_db()

Query 1

SELECT major_name FROM college_majors ORDER by major_name

Query 2

SELECT major_name, early_career_pay FROM college_majors ORDER by "early_career_pay" DESC

Query 3

SELECT major_name, mid_career_pay FROM college_majors ORDER by "mid_career_pay" DESC