Activity 2.20

Source Code

import sqlite3

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

    # 2. Create the names table if it does not exist
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS names (
            name_id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL
        );
    """)

    # 3. Prepare data for 50 popular names
    names_data = [
        "Liam", "Olivia", "Noah", "Emma", "Oliver", "Ava", "Elijah", "Charlotte",
        "William", "Amelia", "James", "Sophia", "Benjamin", "Isabella", "Lucas",
        "Mia", "Henry", "Evelyn", "Alexander", "Harper", "Mason", "Camila",
        "Michael", "Gianna", "Ethan", "Abigail", "Daniel", "Luna", "Matthew",
        "Ella", "Aiden", "Elizabeth", "Joseph", "Sofia", "Jackson", "Emily",
        "Sebastian", "Avery", "David", "Mila", "Carter", "Scarlett", "Wyatt",
        "Eleanor", "Jayden", "Madison", "John", "Layla", "Owen", "Penelope"
    ]

    # Convert list of names to list of tuples, because executemany expects sequences
    # that match the placeholders in the SQL statement
    names_tuples = [(name,) for name in names_data]

    # 4. Insert the data into the names table
    cursor.executemany("INSERT INTO names (name) VALUES (?)", names_tuples)

    # 5. Commit the transaction and close the connection
    conn.commit()
    conn.close()

    print("Successfully created and populated the 'names' table with 50 names.")

if __name__ == "__main__":
    create_and_populate_names_db()

Query 1

SELECT name FROM "names" WHERE name LIKE 'A%' ORDER BY name ASC

Query 2

SELECT name FROM "names" WHERE name LIKE '%a' ORDER BY name ASC

Query 3

SELECT name FROM "names" WHERE name LIKE '%ia%' ORDER BY name ASC