ckashby

students = [
    {"lname": "Kalama", "fname": "Samuel"},
    {"lname": "Kalani", "fname": "Shawn"},
    {"lname": "Kona", "fname": "Sid"},
    {"lname": "Kapena", "fname": "Stan"},
]

ckashby

import sqlite3
import pandas as pd

conn = sqlite3.connect("ladder.db")
# pd.read_sql("SELECT * FROM mytable;", conn)

# pd.read_sql("SELECT * FROM pets;", conn)
# pd.read_sql("SELECT * FROM employees;", conn)
# pd.read_sql("SELECT * FROM transactions;", conn)
pd.read_sql("SELECT * FROM yum;", conn)

ckashby

# ## Summary Statistics
# 32) How many rows are in the `pets` table?
pd.read_sql("SELECT COUNT(*) FROM pets;", conn)
# 13
# 33) How many female pets are in the `pets` table?
pd.read_sql("SELECT COUNT(*) FROM pets WHERE sex = 'F'", conn)
# 7
# 34) How many female cats are in the `pets` table?
pd.read_sql("SELECT COUNT(*) FROM pets WHERE sex = 'F' AND species = 'cat'", conn)
# 4

ckashby

# ## Aggregating Statistics with GROUP BY
# 45) What is the average age of `pets` by species?
pd.read_sql("SELECT AVG(age) FROM pets GROUP BY species;", conn)
# dogs 6.5, cat 4.3, lobster 3 
# 46) Repeat the previous problem but make sure the species label is also displayed! 
pd.read_sql("SELECT species, AVG(age) FROM pets GROUP BY species;", conn)
# Assume this behavior is always being asked of you any time you use `GROUP BY`.
# 47) What is the count, mean, minimum, and maximum age by species in `pets`?
pd.read_sql("SELECT species, COUNT(age), AVG(age), MIN(age), MAX(age) FROM pets GROUP BY species;", conn)

ckashby

# ## Aggregating Statistics with GROUP BY
# 45) What is the average age of `pets` by species?
pd.read_sql("SELECT AVG(age) FROM pets GROUP BY species;", conn)
# dogs 6.5, cat 4.3, lobster 3 
# 46) Repeat the previous problem but make sure the species label is also displayed! 
pd.read_sql("SELECT species, AVG(age) FROM pets GROUP BY species;", conn)
# Assume this behavior is always being asked of you any time you use `GROUP BY`.
# 47) What is the count, mean, minimum, and maximum age by species in `pets`?
pd.read_sql("SELECT species, COUNT(age), AVG(age), MIN(age), MAX(age) FROM pets GROUP BY species;", conn)

ckashby

import sqlite3
import pandas as pd

conn = sqlite3.connect("ladder.db")
# pd.read_sql("SELECT * FROM mytable;", conn)



# pd.read_sql("SELECT * FROM pets;", conn)
# pd.read_sql("SELECT * FROM employees;", conn)

ckashby

import sqlite3
import pandas as pd

conn = sqlite3.connect("ladder.db")
# pd.read_sql("SELECT * FROM mytable;", conn)