<jupyter_start><jupyter_text>The RPG Dataset<jupyter_code>conn = sqlite3.connect('rpg_db (1).sqlite3')
c = conn.cursor()
c.execute('SELECT * FROM charactercreator_character').fetchall()
conn.commit()

query = 'SELECT * FROM charactercreator_character'

pd.read_sql(query, conn)<jupyter_output><empty_output><jupyter_text>How many characters are in the dataset?<jupyter_code># How many characters are in the dataset

c.execute('SELECT COUNT(character_id) FROM charactercreator_character')
result = c.fetchone()
result<jupyter_output><empty_output><jupyter_text>How many tables are in the dataset?<jupyter_code>def get_sql_tables(db_con):
    c = db_con.cursor()
    c.execute("SELECT name FROM sqlite_master WHERE type='table';")
    return c.fetchall()

get_sql_tables(conn)<jupyter_output><empty_output><jupyter_text>How man characters of each subclass?<jupyter_code># How many of each specific subclass

sub_class = ['charactercreator_cleric',
 'charactercreator_fighter',
 'charactercreator_mage',
 'charactercreator_thief',]

def get_subclass_count(db_con, tables):
    c = db_con.cursor()
    c.execute('SELECT COUNT(character_ptr_id) FROM {}'.format(tables))
    return c.fetchone()

for DB in sub_class:
    print(DB, get_subclass_count(conn, DB))
# Alternative way

query_cleric = 'SELECT COUNT(*) FROM charactercreator_character AS cc INNER JOIN charactercreator_cleric AS cleric ON cc.character_id = cleric.character_ptr_id'
c.execute(query_cleric)
result = c.fetchone()
print('cleric', result)

query_mage = 'SELECT COUNT(*) FROM charactercreator_character AS cc INNER JOIN charactercreator_mage AS mage ON cc.character_id = mage.character_ptr_id'
c.execute(query_mage)
result = c.fetchone()
print('mage', result)

query_fighter = 'SELECT COUNT(*) FROM charactercreator_character AS cc INNER JOIN charactercreator_fighter AS fighter ON cc.character_id = fighter.character_ptr_id'
c.execute(query_fighter)
result = c.fetchone()
print('fighter', result)

query_thief = 'SELECT COUNT(*) FROM charactercreator_character AS cc INNER JOIN charactercreator_thief AS thief ON cc.character_id = thief.character_ptr_id'
c.execute(query_thief)
result = c.fetchone()
print('thief', result)<jupyter_output>cleric (75,)
mage (108,)
fighter (68,)
thief (51,)<jupyter_text>How many items are there total?<jupyter_code># How many total items?

query = 'SELECT COUNT(*) FROM armory_item'
c.execute(query).fetchone()<jupyter_output><empty_output><jupyter_text>And Weapons?<jupyter_code># How many are weapons and how many are not

query_weapon = 'SELECT item_id FROM armory_item INNER JOIN armory_weapon ON item_id = item_ptr_id'
print('Weapon count', len(c.execute(query_weapon).fetchall()))

query_non_weapon = 'SELECT item_id FROM armory_item EXCEPT SELECT item_ptr_id FROM armory_weapon'
print('Not weapon', len(c.execute(query_non_weapon).fetchall()))<jupyter_output>Weapon count 37
Not weapon 137<jupyter_text>How many items and weapons per character?<jupyter_code># How many items does each character have

query = 'SELECT character_id as char_id, COUNT(item_id) FROM charactercreator_character_inventory GROUP BY character_id LIMIT 20'
df = pd.read_sql(query, conn)
df
# How many Weapons does each character have

query = 'SELECT character_id, COUNT(item_id) FROM charactercreator_character_inventory INNER JOIN armory_weapon ON item_id = item_ptr_id GROUP BY character_id LIMIT 20 '
df = pd.read_sql(query, conn)
df<jupyter_output><empty_output><jupyter_text>What is the average number of items and weapons carried?<jupyter_code># How many items on Average does each character have

query = '''SELECT AVG(item_count) FROM
(SELECT character_id, COUNT(item_id) as item_count FROM charactercreator_character_inventory GROUP BY character_id) '''
df = pd.read_sql(query, conn)
df
# How many weapons on Average does each character have
# This results in the average from those WITH weapons. Not those without weapons

query = '''SELECT AVG(item_count) FROM
(SELECT character_id, COUNT(item_id) as item_count FROM charactercreator_character_inventory INNER JOIN armory_weapon ON item_id = item_ptr_id GROUP BY character_id) '''
df = pd.read_sql(query, conn)
df

# How many weapons on Average does each character have, INCLUDING those without weapons

query = '''SELECT AVG(wc) FROM (SELECT cci.character_id as `Character Id`, COUNT(aw.item_ptr_id) as wc
FROM charactercreator_character_inventory as cci INNER JOIN armory_item as ai ON cci.item_id = ai.item_id
LEFT JOIN armory_weapon as aw ON ai.item_id = aw.item_ptr_id
GROUP BY cci.character_id)
'''
df = pd.read_sql(query,conn)
df<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09246798350336445, 'fraction_numerical': 0.0045582808769264165, 'mean_word_length': 7.0, 'pattern_counts': {'":': 0, '<': 31, '<?xml version=': 0, '>': 31, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '298430', 'license': 'MIT', 'n_tokens_mistral': 1582, 'n_tokens_neox': 1504, 'n_words': 426, 'path': 'module1-introduction-to-sql/rpg_queries.ipynb', 'repo_name': 'JohnMorrisonn/DS-Unit-3-Sprint-2-SQL-and-Databases', 'size': '4607'}