<gh_stars>0
import sqlite3

conn = sqlite3.connect('rpg_db.sqlite3')

cur = conn.cursor()

#/*How many total Characters are there?*/
cur.execute("SELECT COUNT(character_id) FROM charactercreator_character;")
print('total characters: ',cur.fetchall())

#/*How many of each specific subclass?*/
cur.execute("""SELECT COUNT(character_id) FROM charactercreator_character
WHERE character_id IN (SELECT character_ptr_id FROM charactercreator_mage);""")
print('number of mages: ',cur.fetchall())

cur.execute("""SELECT COUNT(character_id) FROM charactercreator_character
WHERE character_id IN (SELECT character_ptr_id FROM charactercreator_thief);""")
print('number of thieves: ',cur.fetchall())

cur.execute("""SELECT COUNT(character_id) FROM charactercreator_character
WHERE character_id IN (SELECT character_ptr_id FROM charactercreator_cleric);""")
print('number of clerics: ', cur.fetchall())

cur.execute("""SELECT COUNT(character_id) FROM charactercreator_character
WHERE character_id IN (SELECT character_ptr_id FROM charactercreator_fighter);""")
print('number of fighters: ', cur.fetchall())

#/*How many total Items?*/
cur.execute("""SELECT COUNT(item_id) FROM armory_item;""")
print('total items: ',cur.fetchall())

#/*How many of the Items are weapons? How many are not?*/
cur.execute("""SELECT COUNT(item_id) FROM armory_item
WHERE item_id IN (SELECT item_ptr_id FROM armory_weapon);""")
print('total weapons: ',cur.fetchall())

cur.execute("""SELECT COUNT(item_id) FROM armory_item
WHERE item_id NOT IN (SELECT item_ptr_id FROM armory_weapon);""")
print('total non-weapons: ',cur.fetchall())

#/*How many Items does each character have? (Return first 20 rows)*/
cur.execute("""SELECT character_id, COUNT(item_id)
FROM charactercreator_character_inventory
GROUP BY character_id LIMIT 20;""")
print('items per character: ',cur.fetchall())

#/*How many Weapons does each character have? (Return first 20 rows)*/
cur.execute("""SELECT character_id, COUNT(item_id)
FROM charactercreator_character_inventory
WHERE item_id IN (SELECT item_ptr_id FROM armory_weapon)
GROUP BY character_id LIMIT 20;"""
)
print('weapons per character: ',cur.fetchall())

#/*On average, how many Items does each Character have?*/
cur.execute("""SELECT AVG(itemcount) FROM (
SELECT character_id, COUNT(item_id) AS itemcount
FROM charactercreator_character_inventory
GROUP BY character_id LIMIT 20
);""")
print('average items per character: ',cur.fetchall())

#/*On average, how many Weapons does each character have?*/
cur.execute("""SELECT AVG(weaponcount) FROM (
SELECT character_id, COUNT(item_id) AS weaponcount
FROM charactercreator_character_inventory
WHERE item_id IN (SELECT item_ptr_id FROM armory_weapon)
GROUP BY character_id LIMIT 20
);""")
print('average weapons per character: ',cur.fetchall())

cur.close()
import sqlite3

conn = sqlite3.connect('rpg_db.sqlite3')

cur = conn.cursor()

#/*How many total Characters are there?*/
cur.execute("SELECT COUNT(character_id) FROM charactercreator_character;")
print('total characters: ',cur.fetchall())

#/*How many of each specific subclass?*/
cur.execute("""SELECT COUNT(character_id) FROM charactercreator_character
WHERE character_id IN (SELECT character_ptr_id FROM charactercreator_mage);""")
print('number of mages: ',cur.fetchall())

cur.execute("""SELECT COUNT(character_id) FROM charactercreator_character
WHERE character_id IN (SELECT character_ptr_id FROM charactercreator_thief);""")
print('number of thieves: ',cur.fetchall())

cur.execute("""SELECT COUNT(character_id) FROM charactercreator_character
WHERE character_id IN (SELECT character_ptr_id FROM charactercreator_cleric);""")
print('number of clerics: ', cur.fetchall())

cur.execute("""SELECT COUNT(character_id) FROM charactercreator_character
WHERE character_id IN (SELECT character_ptr_id FROM charactercreator_fighter);""")
print('number of fighters: ', cur.fetchall())

#/*How many total Items?*/
cur.execute("""SELECT COUNT(item_id) FROM armory_item;""")
print('total items: ',cur.fetchall())

#/*How many of the Items are weapons? How many are not?*/
cur.execute("""SELECT COUNT(item_id) FROM armory_item
WHERE item_id IN (SELECT item_ptr_id FROM armory_weapon);""")
print('total weapons: ',cur.fetchall())

cur.execute("""SELECT COUNT(item_id) FROM armory_item
WHERE item_id NOT IN (SELECT item_ptr_id FROM armory_weapon);""")
print('total non-weapons: ',cur.fetchall())

#/*How many Items does each character have? (Return first 20 rows)*/
cur.execute("""SELECT character_id, COUNT(item_id)
FROM charactercreator_character_inventory
GROUP BY character_id LIMIT 20;""")
print('items per character: ',cur.fetchall())

#/*How many Weapons does each character have? (Return first 20 rows)*/
cur.execute("""SELECT character_id, COUNT(item_id)
FROM charactercreator_character_inventory
WHERE item_id IN (SELECT item_ptr_id FROM armory_weapon)
GROUP BY character_id LIMIT 20;"""
)
print('weapons per character: ',cur.fetchall())

#/*On average, how many Items does each Character have?*/
cur.execute("""SELECT AVG(itemcount) FROM (
SELECT character_id, COUNT(item_id) AS itemcount
FROM charactercreator_character_inventory
GROUP BY character_id LIMIT 20
);""")
print('average items per character: ',cur.fetchall())

#/*On average, how many Weapons does each character have?*/
cur.execute("""SELECT AVG(weaponcount) FROM (
SELECT character_id, COUNT(item_id) AS weaponcount
FROM charactercreator_character_inventory
WHERE item_id IN (SELECT item_ptr_id FROM armory_weapon)
GROUP BY character_id LIMIT 20
);""")
print('average weapons per character: ',cur.fetchall())

cur.close()
#/*How many total Characters are there?*/ #/*How many of each specific subclass?*/ SELECT COUNT(character_id) FROM charactercreator_character WHERE character_id IN (SELECT character_ptr_id FROM charactercreator_mage); SELECT COUNT(character_id) FROM charactercreator_character WHERE character_id IN (SELECT character_ptr_id FROM charactercreator_thief); SELECT COUNT(character_id) FROM charactercreator_character WHERE character_id IN (SELECT character_ptr_id FROM charactercreator_cleric); SELECT COUNT(character_id) FROM charactercreator_character WHERE character_id IN (SELECT character_ptr_id FROM charactercreator_fighter); #/*How many total Items?*/ SELECT COUNT(item_id) FROM armory_item; #/*How many of the Items are weapons? How many are not?*/ SELECT COUNT(item_id) FROM armory_item WHERE item_id IN (SELECT item_ptr_id FROM armory_weapon); SELECT COUNT(item_id) FROM armory_item WHERE item_id NOT IN (SELECT item_ptr_id FROM armory_weapon); #/*How many Items does each character have? (Return first 20 rows)*/ SELECT character_id, COUNT(item_id) FROM charactercreator_character_inventory GROUP BY character_id LIMIT 20; #/*How many Weapons does each character have? (Return first 20 rows)*/ SELECT character_id, COUNT(item_id) FROM charactercreator_character_inventory WHERE item_id IN (SELECT item_ptr_id FROM armory_weapon) GROUP BY character_id LIMIT 20; #/*On average, how many Items does each Character have?*/ SELECT AVG(itemcount) FROM ( SELECT character_id, COUNT(item_id) AS itemcount FROM charactercreator_character_inventory GROUP BY character_id LIMIT 20 ); #/*On average, how many Weapons does each character have?*/ SELECT AVG(weaponcount) FROM ( SELECT character_id, COUNT(item_id) AS weaponcount FROM charactercreator_character_inventory WHERE item_id IN (SELECT item_ptr_id FROM armory_weapon) GROUP BY character_id LIMIT 20 );