Activity 2.18

Source Code

import sqlite3

# Connect to SQLite database (or create it if it doesn't exist)
conn = sqlite3.connect('activity218.db')
cursor = conn.cursor()

print("Created activity218.db")

# Create the college_majors table
cursor.execute('''
    CREATE TABLE IF NOT EXISTS college_majors (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        major TEXT NOT NULL,
        starting_salary REAL NOT NULL,
        employment_rate REAL NOT NULL
    )
''')

# Data: List of tuples containing (major, starting_salary, employment_rate)
# Employment rate is calculated as (1 - unemployment_rate)
# Data sources: NACE Winter 2024 Salary Survey, Federal Reserve Bank of New York
majors_data = [
    ('Petroleum Engineering', 85000, 0.934),  # 6.6% unemployment rate
    ('Computer Engineering', 76029, 0.963),   # 3.7% unemployment rate
    ('Aerospace Engineering', 77882, 0.934),  # 6.6% unemployment rate
    ('Chemical Engineering', 74440, 0.959),   # 4.1% unemployment rate
    ('Electrical Engineering', 75558, 0.968), # 3.2% unemployment rate
    ('Mechanical Engineering', 72825, 0.947), # 5.3% unemployment rate
    ('Software Engineering', 78482, 0.929),   # 7.1% unemployment rate
    ('Computer Science', 74778, 0.952),       # 4.8% unemployment rate
    ('Civil Engineering', 66732, 0.966),      # 3.4% unemployment rate
    ('Management Information Systems', 73695, 0.936), # 6.4% unemployment rate
    ('Finance', 60776, 0.959),                # 4.1% unemployment rate
    ('Accounting', 59884, 0.967),             # 3.3% unemployment rate
    ('Business Administration', 59514, 0.950),# 5.0% unemployment rate
    ('Marketing', 59652, 0.934),              # 6.6% unemployment rate
    ('Economics', 64193, 0.945),              # 5.5% unemployment rate
    ('Mathematics', 68372, 0.942),            # 5.8% unemployment rate
    ('Physics', 71467, 0.939),                # 6.1% unemployment rate
    ('Chemistry', 66156, 0.966),              # 3.4% unemployment rate
    ('Biology', 58701, 0.953),                # 4.7% unemployment rate
    ('Psychology', 62294, 0.953)              # 4.7% unemployment rate
]

# Insert data into the table
cursor.executemany('''
    INSERT INTO college_majors (major, starting_salary, employment_rate)
    VALUES (?, ?, ?)
''', majors_data)

# Commit the transaction
conn.commit()

# Close the connection
conn.close()

Query 1

SELECT major, starting_salary FROM college_majors WHERE starting_salary > 65000;

Query 2

SELECT major, starting_salary FROM college_majors WHERE starting_salary < 65000