Untitled

import sqlite3

# Connect to the provided SQLite database
db_path = 'sqlite_facex_msba.sqlite'
conn = sqlite3.connect(db_path)

# Create a cursor object
cursor = conn.cursor()

# Query to find the most popular books among profiles who also list “Harry Potter”
query = """
SELECT FavoriteBook, COUNT(*) as count
FROM favoritebooks
WHERE ProfileID IN (
    SELECT ProfileID
    FROM favoritebooks
    WHERE FavoriteBook LIKE '%Harry Potter%'
)
GROUP BY FavoriteBook
ORDER BY count DESC
"""

# Execute the query
cursor.execute(query)

# Fetch all results
results = cursor.fetchall()

# Convert results to a pandas DataFrame for better readability
import pandas as pd

df = pd.DataFrame(results, columns=['FavoriteBook', 'count'])

# Close the connection
conn.close()

# import ace_tools as tools; tools.display_dataframe_to_user(name="Most Popular Books Among Harry Potter Fans", dataframe=df)

# Display the results
print (df)

# Reconnect to the SQLite database
conn = sqlite3.connect(db_path)
cursor = conn.cursor()

# Query to find students who have declared more than one concentration
query = """
SELECT a.ProfileID, a.Concentration AS Concentration1, b.Concentration AS Concentration2
FROM concentration a
JOIN concentration b ON a.ProfileID = b.ProfileID AND a.Concentration < b.Concentration
ORDER BY a.ProfileID
"""

# Execute the query
cursor.execute(query)

# Fetch all results
results = cursor.fetchall()

# Convert results to a pandas DataFrame for better readability
df_concentration_pairs = pd.DataFrame(results, columns=['ProfileID', 'Concentration1', 'Concentration2'])

# Close the connection
conn.close()

# Display the results
print (df_concentration_pairs)