SELECT 
    l.Title,
    r.Name
FROM 
    albums AS l
INNER JOIN
    artists AS r
ON 
    r.ArtistId = l.ArtistId;

SELECT
   Title, 
   Name
FROM
   albums
INNER JOIN artists USING(ArtistId);

SELECT
    Name, 
    Title
FROM
    artists
LEFT JOIN albums ON artists.ArtistId = albums.ArtistId
ORDER BY 
    Name;

SELECT m.firstname || ' ' || m.lastname AS 'Manager',
       e.firstname || ' ' || e.lastname AS 'Receives reports from'
FROM
    employees e
INNER JOIN 
    employees m 
ON 
    m.employeeid = e.reportsto
ORDER BY 
    manager;

SELECT 10 / 5, 2 * 4;

SELECT trackid, name FROM tracks;

SELECT * FROM tracks;

INSERT INTO artists (name) VALUES('Bud Powell');

script = """
INSERT INTO artists (name) VALUES ("?");
"""

data = [
    ("Buddy Rich"),
    ("Candido"),
    ("Charlie Byrd")
]

cur.executemany(script, data)

SELECT
    ArtistId,
    Name
FROM
    Artists
ORDER BY
    ArtistId DESC;

UPDATE employees SET lastname = 'Smith' WHERE employeeid = 3;

UPDATE employees
SET city = 'Toronto', 
    state = 'ON',
    postalcode = 'M5P 2N7'
WHERE
    employeeid = 4;

UPDATE employees
SET email = UPPER(firstname || "." || lastname || "@corp.co.kr");

SELECT 
    TrackId, 
    Name, 
    Composer 
FROM 
   tracks
ORDER BY 
   Composer;

import sqlite3

dbpath = "maindb.db"
conn = sqlite3.connect(dbpath)
cur = conn.cursor()

CREATE TABLE devices (
   name TEXT NOT NULL,
   model TEXT NOT NULL,
   Serial INTEGER NOT NULL UNIQUE

CREATE TABLE contact_groups(
   contact_id INTEGER,
   group_id INTEGER,
   PRIMARY KEY (contact_id, group_id), 
   FOREIGN KEY (contact_id) 
      REFERENCES contacts(contact_id)
         ON DELETE CASCADE,
   FOREIGN KEY (group_id) 
      REFERENCES groups(group_id)
         ON DELETE CASCADE
);

ALTER TABLE devices RENAME TO equipment;

ALTER TABLE equipment 
ADD COLUMN location text;

ALTER TABLE equipment 
RENAME COLUMN location TO loc;

DROP TABLE equipment ;

SELECT 
    name
FROM 
    sqlite_master 
WHERE
    type ='table' AND 
    name NOT LIKE 'sqlite_%'; 

cac = ad_df['Marketing_Costs'].sum() / ad_df['User_Acquired'].sum()
print(cac * 10000)

# Output
446원

import pandas as pd
import seaborn as sns

import scipy as sp
from scipy import stats

import warnings
warnings.filterwarnings("ignore")

crosstab = pd.crosstab(df.propensity, df.skin, margins=True)

crosstab.columns=[]
crosstab.index=[]

stats.chisquare(df.column1, df.column2)

# Output
Power_divergenceResult(statistic=291.8166666666667, pvalue=0.023890557260065975)

crosstab.plot.bar(stacked=True)

df['column'].value_counts().plot(kind = 'pie')

df['column'].value_counts().plot(kind = 'bar')

import smtplib
from email.mime.text import MIMEText

def sendMail(sender, receiver, msg):
    smtp = smtplib.SMTP_SSL('smtp.gmail.com', 465)
    smtp.login(sender, 'your google app password')
    
    msg = MIMEText(msg)
    msg['Subject'] = 'Product is available!'
    
    smtp.sendmail(sender, receiver, msg.as_string())
    smtp.quit()

# 라이브러리 선언

check_status = 1
url = 'https://www.wadiz.kr/web/campaign/detail/{item_number}'

# 상품 재고가 확인되어 메일이 발송되면 종료
while check_status:

    webpage = urlopen(url)
    source = BeautifulSoup(webpage, 'html.parser')
    target = source.find_all('button', {'class':'rightinfo-reward-list'})

    for item in target:
        # 가격이 '179,000'원 상품 중
        if '179,000' in item.find('dt').get_text().strip():
            # '블루' 색상인 상품에 대하여
            if '블루' in item.find('p').get_text().strip():
                # 판매 중인 상태가 되면 (마감된 상품엔 "soldout" 클래스가 추가)
                if len(item.attrs['class']) == 2:
                        sendMail(sender, receiver, msg)
                        check_status = 0

url = 'https://golmok.seoul.go.kr/regionAreaAnalysis.do'

response = requests.get(url).content
web_page = BeautifulSoup(response, 'html.parser')

# Payload 설정
data = {'stdrYyCd': '2021', 'stdrQuCd': '4', 'stdrSlctQu': 'sameQu', 'svcIndutyCdL': 'CS000000', 'svcIndutyCdM': 'all'}

response = requests.post('https://golmok.seoul.go.kr/region/selectRentalPrice.json', data=data).content

result = json.loads(response)

# 크롬 드라이버 파일 자동 다운로드
from webdriver_manager.chrome import ChromeDriverManager
# 크롬 드라이버를 파일에 연결
from selenium.webdriver.chrome.service import Service

from selenium import webdriver
from selenium.webdriver.common.by import By
from selenium.webdriver.common.keys import Keys

from bs4 import BeautifulSoup 
import time
import pandas as pd

import warnings
warnings.filterwarnings("ignore") # 불필요한 Warning 메시지 무시

# 크롬 드라이버 파일을 다운로드 후 세팅
service = Service(executable_path=ChromeDriverManager().install()) 

# 세팅된 크롬 드라이버를 연결해 가상 브라우저 실행
driver = webdriver.Chrome(service=service)

translate_url = 'https://translate.google.co.kr/?sl=auto&tl=en&op=translate&hl=ko'

driver.get(translate_url)

origin_xpath = '원본 텍스트 부분에 해당하는 XPath'

driver.find_element_by_xpath(origin_xpath).clear()
driver.find_element_by_xpath(origin_xpath).send_keys('원본 텍스트')

translation_xpath = '번역된 텍스트 부분에 해당하는 XPath'

translated_contents = driver.find_element_by_xpath(translation_xpath).text

driver.close()
driver.quit()

for key in translation_target:
    
    # key를 원본 텍스트 부분에 입력
    time.sleep(3)

    # translated_contents 변수에 번역된 텍스트를 가져와서 저장
    
    translation_result[translated_contents] = translation_target[key]

driver.close()
driver.quit()