Activity 2.21

Source Code

import sqlite3

# Connect to the database
conn = sqlite3.connect('activity221.db')
cursor = conn.cursor()

# Create the students table
cursor.execute('''
    CREATE TABLE IF NOT EXISTS students (
        id INTEGER PRIMARY KEY,
        name TEXT,
        major TEXT
    )
''')

# Insert the data
students_data = [
    (1, 'Alice', 'Computer Science'),
    (2, 'Bob', 'Mathematics'),
    (3, 'Alice', 'Computer Science'),
    (4, 'David', 'Physics'),
    (5, 'Alice', 'Biology')
]

# Insert data into table
cursor.executemany('INSERT INTO students (id, name, major) VALUES (?, ?, ?)', students_data)

# Commit changes
conn.commit()

# Create the video_games table
cursor.execute('''
    CREATE TABLE IF NOT EXISTS video_games (
        id INTEGER PRIMARY KEY,
        student_name TEXT,
        game_title TEXT,
        genre TEXT
    )
''')

# Insert expanded dataset
video_games_data = [
    (1, 'Alice', 'Minecraft', 'Sandbox'),
    (2, 'Bob', 'Fortnite', 'Battle Royale'),
    (3, 'Alice', 'Minecraft', 'Sandbox'),
    (4, 'David', 'Rocket League', 'Sports'),
    (5, 'Emma', 'Fortnite', 'Battle Royale'),
    (6, 'Bob', 'Among Us', 'Party'),
    (7, 'Alice', 'The Sims 4', 'Simulation'),
    (8, 'Charlie', 'Rocket League', 'Sports'),
    (9, 'Emma', 'Animal Crossing', 'Life Sim'),
    (10, 'David', 'Fortnite', 'Battle Royale'),
    (11, 'Sophia', 'Stardew Valley', 'Farming Sim'),
    (12, 'Ethan', 'Call of Duty', 'FPS'),
    (13, 'Charlie', 'Halo Infinite', 'FPS'),
    (14, 'Sophia', 'League of Legends', 'MOBA'),
    (15, 'Alice', 'Roblox', 'Sandbox'),
    (16, 'Bob', 'Valorant', 'FPS'),
    (17, 'Emma', 'Overwatch', 'FPS'),
    (18, 'Ethan', 'Apex Legends', 'Battle Royale'),
    (19, 'Charlie', 'Genshin Impact', 'RPG'),
    (20, 'Alice', 'Animal Crossing', 'Life Sim'),
    (21, 'David', 'Super Smash Bros.', 'Fighting'),
    (22, 'Bob', 'Super Smash Bros.', 'Fighting'),
    (23, 'Ethan', 'Elden Ring', 'RPG'),
    (24, 'Charlie', 'Stardew Valley', 'Farming Sim'),
    (25, 'Sophia', 'Pokemon Scarlet', 'RPG'),
    (26, 'Emma', 'Terraria', 'Sandbox'),
    (27, 'Alice', 'Terraria', 'Sandbox'),
    (28, 'Bob', 'FIFA 23', 'Sports'),
    (29, 'David', 'The Legend of Zelda', 'Action Adventure'),
    (30, 'Ethan', 'Red Dead Redemption 2', 'Action Adventure')
]

# Insert data into table
cursor.executemany('INSERT INTO video_games (id, student_name, game_title, genre) VALUES (?, ?, ?, ?)', video_games_data)

# Commit changes
conn.commit()
print("Tables created!")

# Close the connection
conn.close()

Query 1

SELECT DISTINCT column_name FROM table_name;

Query 2

SELECT DISTINCT column_name FROM table_name;

Query 3

SELECT DISTINCT name, major FROM students;