gistfile1.txt

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)



conn.execute("DROP VIEW IF EXISTS yum_by_month;")




# Finally, sort this data so it's in proper chronological order.
pd.read_sql("""
SELECT 
    strftime('%Y', date) AS year, 
    strftime('%m', date) AS month, 
    AVG(open) AS avg_open, 
    AVG(high) AS avg_high, 
    AVG(low) AS avg_low, 
    AVG(close) AS avg_close, 
    SUM(volume) AS total_volume 
FROM yum 
GROUP BY year, month;
""", conn)




conn.execute("DROP VIEW IF EXISTS yum_by_month;")




# 64) Save the results of the previous query as a view named `yum_by_month`.
conn.execute("""
CREATE VIEW yum_by_month AS
SELECT 
    strftime('%Y', date) AS year, 
    strftime('%m', date) AS month, 
    AVG(open) AS avg_open, 
    AVG(high) AS avg_high, 
    AVG(low) AS avg_low, 
    AVG(close) AS avg_close, 
    SUM(volume) AS total_volume 
FROM yum 
GROUP BY year, month;
""")




# 64) Save the results of the previous query as a view named `yum_by_month`.
pd.read_sql("SELECT * FROM yum_by_month;", conn)




# 65) Create a view of `transactions` consisting of only three columns: year, month, and total sales in that month. 
# Call this view `trans_by_month`.
pd.read_sql("""SELECT * FROM transactions;""", conn)






# 65) Create a view of `transactions` consisting of only three columns: year, month, and total sales in that month. 
# Call this view `trans_by_month`.
conn.execute("DROP VIEW IF EXISTS trans_by_month")





# 65) Create a view of `transactions` consisting of only three columns: year, month, and total sales in that month. 
# Call this view `trans_by_month`.
conn.execute(""" 
CREATE VIEW trans_by_month
AS
SELECT 
    strftime('%Y', orderdate) AS year, 
    strftime('%m', orderdate) AS month, 
    SUM(unit_price * quantity) AS 'total sales'
FROM transactions
GROUP BY year, month;
""")
                
                
                



pd.read_sql("SELECT * FROM trans_by_month;", conn)





# 66) Create a view of `transactions` consisting of only two columns: `employee_id` and the total sales corresponding to that employee. Call this view `trans_by_employee`.
conn.execute("""
CREATE VIEW
'trans_by_employee' AS
SELECT 
    employee_id, 
    SUM(unit_price * quantity) AS 'total sales'
    FROM transactions
    GROUP BY employee_id;
""")




pd.read_sql("SELECT * FROM trans_by_employee;", conn)