SQLZOO中文版答案记录/NSS Tutorial

1、这里不需要再额外做百分比了，因为A_STRONGLY_AGREE显示的数值就是百分比，不清楚的可以用

SELECT * FROM nss 看一下

SELECT A_STRONGLY_AGREE
  FROM nss
 WHERE question='Q01'
   AND institution='Edinburgh Napier University'
   AND subject='(8) Computer Science'

2

SELECT institution,subject
  FROM nss
 WHERE question='Q15'
   AND score>=100

3

SELECT institution,score
  FROM nss
 WHERE question='Q15'
   AND score<50
   AND subject='(8) Computer Science'

4

SELECT subject, sum(response)
  FROM nss
 WHERE question='Q22' AND 
    (subject='(8) Computer Science'
    OR subject= '(H) Creative Arts and Design')
 GROUP BY subject

5、这个问题在于求总人数，而强烈同意的是占比，所以要乘上回答的总人数*0.01，但是由于乘上之后会有小数，需要floor函数先把每个数向下取整，再加和。

SELECT subject, sum( floor(A_STRONGLY_AGREE*response*0.01))
  FROM nss
 WHERE question='Q22'
    AND (subject='(8) Computer Science'
    OR subject='(H) Creative Arts and Design')
 GROUP BY subject

6、这里用round其实是有异议的，因为round是四舍五入，如果在上一题中用round而不是floor，值会差很多。第2种方法勉强可以做出和1同样的结果，但是我认为1更符合。

SELECT subject, 
floor(sum(floor(A_STRONGLY_AGREE*response*0.01))/sum(response)*100)
  FROM nss
WHERE question='Q22'
    AND (subject='(8) Computer Science'
    OR subject='(H) Creative Arts and Design')
 GROUP BY subject

SELECT subject, 
floor(sum(round(A_STRONGLY_AGREE*response*0.01,0))/sum(response)*100)
  FROM nss
WHERE question='Q22'
    AND (subject='(8) Computer Science'
    OR subject='(H) Creative Arts and Design')
 GROUP BY subject

7、这里求平均不能用avg，因为avg是把所有平均分数（score）再平均，而这里是要把所有分数加和后再平均。

SELECT institution,round(sum(score*response)/sum(response),0)
  FROM nss
 WHERE question='Q22'
   AND (institution LIKE '%Manchester%')
GROUP BY institution

8、两种条件求出两个合计数，思路就是分别求出两个数据再拼合成一张表，没有想到什么更简便的方案。用union的话是竖向合并，这里不合适。

SELECT A.institution,A.sample,B.student FROM 
(select institution, sum(sample) as sample
  from nss
 where question='Q01'
   and (institution like '%Manchester%')
group by institution) AS A
JOIN
(select institution, sum(sample) as student
  from nss
 where question='Q01'
   and (institution like '%Manchester%')
   and (subject like '%Computer%')
group by institution) AS B
ON (A.institution=B.institution)