LAB 10 SQL

Recording
Slides
Type
LAB
在notebook中，写db的推荐方式为：
  1. 先 用 %%sql 来在cell中写sql语句
  1. 然后再把语句放到 pd.read_sql中
从两张表中select col 一般有两种写法，一种是合并两张表，另外一种是直接select
# way 1
SELECT Title.primaryTitle AS title, CAST(Rating.numVotes AS int) AS numVotes,Rating.averageRating
FROM Rating 
JOIN Title ON Rating.tconst = Title.tconst

# way 2
SELECT Title.primaryTitle AS title,CAST(Rating.numVotes AS int) AS numVotes,Rating.averageRating
FROM Title, Rating 
where Title.tconst = Rating.tconst
 
 
先用 with xx as 生成xx 表，然后再在xx表格中select
FROM indiv_sample_nyc AS i 
INNER JOIN comm AS c2
ON  i.cmte_id = c2.cmte_id
WHERE name LIKE '%TRUMP%' AND name LIKE '%DONALD%' AND name NOT LIKE '%INC%'
)
SELECT cmte_id, 
cmte_nm,
SUM(transaction_amt) AS total_amount, 
COUNT(transaction_amt) AS num_donations
FROM donations
GROUP BY cmte_id
ORDER BY total_amount DESC;