sqlzoo 8+.Numeric Examples 答案

有什么问题，欢迎评论或私聊。转载请私聊博主，谢谢。

原题链接：https://sqlzoo.net/wiki/NSS_Tutorial

其他题解连接：https://blog.csdn.net/aiqiyizz/article/details/109057732

题解对应的是英文版题目。

8+ Numeric Examples

该数据库来源是一个对成千上万个来自英国高等教育的学生的调查。调查询问了22个问题，学生可以回答STRONGLY DISAGREE, DISAGREE, NEUTRAL, AGREE or STRONGLY AGREE。存储值的形式是用这部分学生占所有学生的PERCENTAGES（百分数）。

table名字叫做nss，保证(机构、科目、问题)组合唯一。

你问我为什么翻译这段？因为不翻译我不知道第一题中的百分数哪来的。。

8+.1 Check out one row

SELECT institution,subject,score,response
  FROM nss
 WHERE question='Q22'
   AND (institution LIKE '%Manchester%')

8+.2 Calculate how many agree or strongly agree

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

8+.3 Unhappy Computer Students

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

8+.4 More Computing or Creative Students?

需要找题目数据所在行

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

8+.5 Strongly Agree Numbers

求人数$百分比\times 总人数÷100\Rightarrow$求和得总人数

正解方式计算出的人数为小数形式，严谨些需要添加ROUND做限制。

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

8+.6 Strongly Agree, Percentage

$人数百分比=所占人数÷总人数\times 100$

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

8+.7 Scores for Institutions in Manchester

求的是加权平均数，不能直接用AVG

SELECT institution, ROUND(SUM(score*response)/SUM(response)) AS score
  FROM nss
 WHERE question='Q22'
   AND (institution LIKE '%Manchester%')
 GROUP BY institution

8+.8 Number of Computing Students in Manchester

computing students：计算机专业学生，联想到前面的subject。

使用SUM+CASE WHEN进行计算

SELECT institution, SUM(sample) AS sample, SUM(CASE subject WHEN '(8) Computer Science' THEN sample ELSE 0 END) AS 'computing students'
  FROM nss
 WHERE question='Q01'
   AND (institution LIKE '%Manchester%')
 GROUP BY institution