<jupyter_start><jupyter_text>Assignment - Part 1, Querying a Database---This directory contains a file `rpg_db.sqlite3`, a database for a hypotheticalwebapp role-playing game. This test data has dozens-to-hundreds of randomlygenerated characters across the base classes (Fighter, Mage, Cleric, and Thief)as well as a few Necromancers. Also generated are Items, Weapons, andconnections from characters to them. Note that, while the name field wasrandomized, the numeric and boolean fields were left as defaults.Use `sqlite3` to load and write queries to explore the data, and answer thefollowing questions:- How many total Characters are there?- How many of each specific subclass?- How many total Items?- How many of the Items are weapons? How many are not?- How many Items does each character have? (Return first 20 rows)- How many Weapons does each character have? (Return first 20 rows)- On average, how many Items does each Character have?- On average, how many Weapons does each character have?You do not need all the tables - in particular, the `account_*`, `auth_*`,`django_*`, and `socialaccount_*` tables are for the application and do not havethe data you need. the `charactercreator_*` and `armory_*` tables and where youshould focus your attention. `armory_item` and `charactercreator_character` arethe main tables for Items and Characters respectively - the other tables aresubsets of them by type (i.e. subclasses), connected via a key (`item_id` and`character_id`).You can use the DB Browser or other tools to explore the data and work on yourqueries if you wish, but to complete the assignment you should write a file`rpg_queries.py` that imports `sqlite3` and programmatically executes andreports results for the above queries.Some of these queries are challenging - that's OK! You can keep working on themtomorrow as well (we'll visit loading the same data into PostgreSQL). It's alsoOK to figure out the results partially with a query and partially with a bit oflogic or math afterwards, though doing things purely with SQL is a good goal.[Subqueries](https://www.w3resource.com/sql/subqueries/understanding-sql-subqueries.php)and [aggregation functions](https://www.sqltutorial.org/sql-aggregate-functions/)may be helpful for putting together more complicated queries. 1. How many total Characters are there?<jupyter_code>results = select_all_query('rpg_db.sqlite3', 'SELECT COUNT(*) FROM charactercreator_character')
print('How many total Characters are there? ', results[0][0])<jupyter_output>How many total Characters are there?  302<jupyter_text>2. How many of each specific subclass? **mage**<jupyter_code>results = select_all_query('rpg_db.sqlite3', 'SELECT COUNT(*) FROM charactercreator_character as cc INNER JOIN charactercreator_mage as mage on cc.character_id = mage.character_ptr_id')
print('How many of each mage subclass? ', results[0][0])<jupyter_output>How many of each mage subclass?  108<jupyter_text>**thief**<jupyter_code>results = select_all_query('rpg_db.sqlite3', 'SELECT COUNT(*) FROM charactercreator_character as cc INNER JOIN charactercreator_thief as thief on cc.character_id = thief.character_ptr_id')
print('How many of each thief subclass? ', results[0][0])<jupyter_output>How many of each thief subclass?  51<jupyter_text>**cleric**<jupyter_code>results = select_all_query('rpg_db.sqlite3', 'SELECT COUNT(*) FROM charactercreator_character as cc INNER JOIN charactercreator_cleric as cleric on cc.character_id = cleric.character_ptr_id')
print('How many of each cleric subclass? ', results[0][0])<jupyter_output>How many of each cleric subclass?  75<jupyter_text>**fighter**<jupyter_code>results = select_all_query('rpg_db.sqlite3', 'SELECT COUNT(*) FROM charactercreator_character as cc INNER JOIN charactercreator_fighter as fighter on cc.character_id = fighter.character_ptr_id')
print('How many of each fighter subclass? ', results[0][0])<jupyter_output>How many of each fighter subclass?  68<jupyter_text>3. How many total Items?<jupyter_code>results = select_all_query('rpg_db.sqlite3', 'SELECT COUNT(*) FROM armory_item')
print('How many total items?', results[0][0])<jupyter_output>How many total items? 174<jupyter_text>4. How many of the Items are weapons? How many are not?<jupyter_code>results = select_all_query('rpg_db.sqlite3', 'SELECT COUNT(*) FROM armory_item as item INNER JOIN armory_weapon as weapon ON item.item_id = weapon.item_ptr_id')
print('How many of the items are weapons? ', results[0][0])
results = select_all_query('rpg_db.sqlite3', 'SELECT (SELECT COUNT(*) FROM armory_item) - (SELECT COUNT(*) FROM armory_item as item INNER JOIN armory_weapon as weapon ON item.item_id = weapon.item_ptr_id)')
print('How many of the items are not weapons? ', results[0][0])<jupyter_output>How many of the items are not weapons?  137<jupyter_text>5. How many Items does each character have? (Return first 20 rows)<jupyter_code>query = '''SELECT character_id as `Character Id`, COUNT(item_id) as `Item Count` 
FROM charactercreator_character_inventory 
GROUP BY character_id LIMIT 20'''
conn = create_connection('rpg_db.sqlite3')
df = pd.read_sql(query, conn)
df.shape<jupyter_output><empty_output><jupyter_text>6. How many Weapons does each character have? (Return first 20 rows)<jupyter_code>query = '''SELECT cci.character_id as `Character Id`, COUNT(aw.item_ptr_id) as `Weapon Count`
FROM charactercreator_character_inventory as cci
INNER JOIN armory_item as ai ON cci.item_id = ai.item_id
INNER JOIN armory_weapon as aw ON ai.item_id = aw.item_ptr_id
GROUP BY cci.character_id
LIMIT 20;'''
conn = create_connection('rpg_db.sqlite3')
df = pd.read_sql(query, conn)
df<jupyter_output><empty_output><jupyter_text>7. On average, how many Items does each Character have?<jupyter_code>query = '''SELECT character_id as `Character Id`, COUNT(item_id) as `Item Count` 
FROM charactercreator_character_inventory 
GROUP BY character_id'''
conn = create_connection('rpg_db.sqlite3')
df = pd.read_sql(query, conn)
df.describe()
query = '''SELECT AVG(c)
FROM(
SELECT character_id, COUNT(item_id) as c
FROM charactercreator_character_inventory
GROUP BY character_id
)
'''
conn = create_connection('rpg_db.sqlite3')
df = pd.read_sql(query, conn)
df
query = """SELECT AVG(counted) FROM (
SELECT character_id, item_id, COUNT(*) AS counted
FROM charactercreator_character_inventory
GROUP BY character_id);
"""
conn = create_connection('rpg_db.sqlite3')
df = pd.read_sql(query, conn)
df<jupyter_output><empty_output><jupyter_text>8. On average, how many Weapons does each character have?<jupyter_code>query = '''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;'''
conn = create_connection('rpg_db.sqlite3')
df = pd.read_sql(query, conn)
df.describe()
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)
'''
conn = create_connection('rpg_db.sqlite3')
df = pd.read_sql(query, conn)
df
# Will's Answer

query = """SELECT AVG(counted) FROM (
SELECT character_id, item_id, COUNT(*) AS counted
FROM charactercreator_character_inventory
WHERE item_id IN (SELECT item_ptr_id FROM armory_weapon)
GROUP BY character_id);
"""
conn = create_connection('rpg_db.sqlite3')
df = pd.read_sql(query, conn)
df<jupyter_output><empty_output><jupyter_text>Assigment - Part 2, Making and populating a Database---Load the data (use `pandas`) from the provided file `buddymove_holidayiq.csv`(the [BuddyMove DataSet](https://archive.ics.uci.edu/ml/datasets/BuddyMove+Data+Set)) - you shouldhave 249 rows, 7 columns, and no missing values. The data reflects the number ofplace reviews by given users across a variety of categories (sports, parks,malls, etc.).Using the standard `sqlite3` module:- Open a connection to a new (blank) database file `buddymove_holidayiq.sqlite3`- Use `df.to_sql`  ([documentation](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html))  to insert the data into a new table `review` in the SQLite3 databaseThen write the following queries (also with `sqlite3`) to test:- Count how many rows you have - it should be 249!- How many users who reviewed at least 100 `Nature` in the category also  reviewed at least 100 in the `Shopping` category?- (*Stretch*) What are the average number of reviews for each category?Your code (to reproduce all above steps) should be saved in`buddymove_holidayiq.py`, and added to the repository along with the generatedSQLite database.<jupyter_code># Create database file if it doesn't exist
with sqlite3.connect('buddymove_holidayiq.sqlite3') as conn:
    
    # 1. Read csv file
    df = pd.read_csv('buddymove_holidayiq.csv')
    
    # 2. DROP TABLE review IF EXISTS
    drop_query = 'DROP TABLE IF EXISTS review'
    conn.cursor().execute(drop_query)
    
    # 3. INSERT TABLE review
    df.to_sql('review', conn, index=False)
    query = 'SELECT * FROM review'
    df = pd.read_sql(query, conn)
df.head()<jupyter_output>/home/asura/anaconda3/envs/practicalml/lib/python3.7/site-packages/pandas/core/generic.py:2130: UserWarning: The spaces in these column names will not be changed. In pandas versions < 0.14, spaces were converted to underscores.
  dtype=dtype)<jupyter_text>- Count how many rows you have - it should be 249!<jupyter_code>query = '''SELECT COUNT(*) FROM review;'''
select_all_query('buddymove_holidayiq.sqlite3', query)[0][0]<jupyter_output><empty_output><jupyter_text>- How many users who reviewed at least 100 Nature in the category also reviewed at least 100 in the Shopping category?<jupyter_code>query = '''SELECT *
FROM review
WHERE `Nature` >= 100 AND `Shopping` >= 100
'''
conn = create_connection('buddymove_holidayiq.sqlite3')
df = pd.read_sql(query, conn)
df.head()<jupyter_output><empty_output><jupyter_text>- (Stretch) What are the average number of reviews for each category?<jupyter_code>query = '''SELECT *
FROM review
'''
conn = create_connection('buddymove_holidayiq.sqlite3')
df = pd.read_sql(query, conn)
df.describe()
query = '''SELECT AVG(Sports), AVG(Religious), AVG(Nature), AVG(Theatre), AVG(Shopping), AVG(Picnic)
FROM review
'''
conn = create_connection('buddymove_holidayiq.sqlite3')
df = pd.read_sql(query, conn)
df
def get_sql_tables(db_con):
    c = db_con.cursor()
    c.execute("SELECT name FROM sqlite_master WHERE type='table';")
    return c.fetchall()
conn = create_connection('buddymove_holidayiq.sqlite3')
get_sql_tables(conn)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09824074074074074, 'fraction_numerical': 0.012037037037037037, 'mean_word_length': 6.12467018469657, 'pattern_counts': {'":': 0, '<': 54, '<?xml version=': 0, '>': 55, 'https://': 4, 'lorem ipsum': 0, 'www.': 2, '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': '15', 'dir': 'jupyter-structured-clean-dedup', 'id': '613257', 'license': 'MIT', 'n_tokens_mistral': 3582, 'n_tokens_neox': 3402, 'n_words': 1135, 'path': 'module1-introduction-to-sql/SQlite + Python.ipynb', 'repo_name': 'ameralhomdy/DS-Unit-3-Sprint-2-SQL-and-Databases', 'size': '10800'}