<jupyter_start><jupyter_text>Q1-1 Q1-2<jupyter_code>driver=webdriver.Chrome("C://Users/minji hong/chromedriver/chromedriver.exe")
driver.get("https://twitter.com/search?q=%23asiancup)%EB%A5%BC")
driver.implicitly_wait(3)<jupyter_output><empty_output><jupyter_text>Q2-1products.db에 존재하는 모든 table과 column을 조회하세요.<jupyter_code>db= lite.connect('./products.db')
query="""SELECT *FROM sqlite_master
;""" 
pd.read_sql(query,db)<jupyter_output><empty_output><jupyter_text>Q2-2Customer의 Country가 "Germany" 이거나 City가 "London" 인 모든 주문의 주문양(Quantity)의 합을 조회하세요.<jupyter_code>query="""
SELECT Quantity

FROM OrderDetails

WHERE
OrderID IN
(SELECT OrderID
FROM Orders
WHERE
    CustomerID IN(SELECT CustomerID
    FROM Customers
    Where Country =='Germany' OR City == 'London'))
;
"""
pd.read_sql(query,db)<jupyter_output><empty_output><jupyter_text>Q2-3¶월별 총 주문 횟수와 총 주문 갯수, 평균 주문 금액(소수점 2째 자리에서 반올림)을 조회하세요. (총 주문 횟수를 기준으로 내림차순 정렬)<jupyter_code>query = """
SELECT  substr(O.OrderDate,1,7)'MONTH',ProductID,Quantity

FROM
    OrderDetails D
    JOIN
        Orders O
        ON
            O.OrderID = D.OrderID
GROUP BY
        substr(O.OrderDate,1,7)
;
"""
pd.read_sql(query, db).head() 
#### 주문 금액 Product 테이블에서 금액을 가져오되 product ID를 기준으로 할것

query="""
SELECT ProductID,ProductName,Price
FROM Products
WHERE ProductID IN
(SELECT ProductID
FROM OrderDetails)

"""
pd.read_sql(query,db)

query = """
SELECT *

FROM
    OrderDetails D
    JOIN
        Products P
        ON
            P.ProductID = D.ProductID
;
"""
pd.read_sql(query, db).head()
query = """
SELECT Quantity,ProductName,Price

FROM
    OrderDetails D
    JOIN
        Products P
        ON
            P.ProductID = D.ProductID
;
"""
pd.read_sql(query, db).head()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.128, 'fraction_numerical': 0.010857142857142857, 'mean_word_length': 4.165191740412979, 'pattern_counts': {'":': 0, '<': 17, '<?xml version=': 0, '>': 17, 'https://': 1, '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': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '595744', 'license': 'MIT', 'n_tokens_mistral': 761, 'n_tokens_neox': 832, 'n_words': 135, 'path': 'Assignment/vacation_assignment.ipynb', 'repo_name': 'MinJiH/TIL', 'size': '1750'}