数据库5个小问题，求大佬，可以的话顺便讲解一下

补充：没想到csdn的编辑器这么差，我是有断行的，发出去全串成一句了[无辜脸]

第1,2题简单不解释，
第3题：字符串匹配用like '%Coffee%'，表示字符串存在Coffee字段，not like就是没有啦
第4题：用with tmp as (select 子句) 来预先查点东西（视图），避免挤在一个句子里，这里先数了一下评价数和统计了一下评分和，题目要求10条以上评论并且这些评论都是5星，所以后面用了“评价数*5=评分和”来做判定
第5题：麻烦点，连着创建两个临时视图，其实一样的，预先做点统计而已

1. select count(distinct(category)) as count_categories from business_category
2. select count(bid) as count_business_PA from business where city='PA'
3. select t1.bid, t1.name from business as t1 left join business_category as t2 on t1.bid=t2.bid where t1.name like '%Coffee%' and t2.category not like '%Coffee%' and t1.city='PA' order by bid
4. with tmp as ( select t1.bid, t2.name, count(*) as review_counts, sum(stars) as sum_star from review as t1 left join business as t2 on t1.bid=t2.bid where t2.city='PA' group by bid) select bid, name, review_count from tmp where review_count>10 and review_count*5=sum_star order by review_count desc, bid
   1. with tmp as ( select uid, count(*) as hit_count from review group by uid), tmp2 as ( select bid, count(*) as onehit_count from tmp left join review on tmp.uid=review.uid where tmp.hit_count=1 and review.stars=5.0 group by bid) select tmp2.bid, name, onehit_count from tmp2 left join business on tmp2.bid=business.bid where onehit_count>150 order by onehit_count desc, bid

我是多么希望能拿到10分那，要下个东西，求分ing。。。。