SQLZOO:8+ Numeric Examples

1.The example shows the number who responded for:

question 1
at ‘Edinburgh Napier University’
studying ‘(8) Computer Science’
Show the the percentage who STRONGLY AGREE

SELECT round(A_STRONGLY_AGREE/
(A_STRONGLY_DISAGREE+
A_DISAGREE+
A_NEUTRAL+
A_AGREE+
A_STRONGLY_AGREE)*100,0)
FROM nss
WHERE question='Q01'
AND institution='Edinburgh Napier University'
AND subject='(8) Computer Science'

• 用到之前有一章里用过的取百分比的方法。

2.Show the institution and subject where the score is at least 100 for question 15.

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

3.Show the institution and score where the score for ‘(8) Computer Science’ is less than 50 for question ‘Q15’

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

• 和牛客的比起来sqlzoo的题真的简单好多。在牛客被打击了就来sqlzoo找找自信吧

4.Show the subject and total number of students who responded to question 22 for each of the subjects ‘(8) Computer Science’ and ‘(H) Creative Arts and Design’.
HINT
You will need to use SUM over the response column and GROUP BY subject

SELECT subject,sum(response)
  FROM nss
 WHERE question='Q22'
   AND subject in ('(8) Computer Science','(H) Creative Arts and Design')
group by subject

5.Show the subject and total number of students who A_STRONGLY_AGREE to question 22 for each of the subjects ‘(8) Computer Science’ and ‘(H) Creative Arts and Design’.

HINT
The A_STRONGLY_AGREE column is a percentage. To work out the total number of students who strongly agree you must multiply this percentage by the number who responded (response) and divide by 100 - take the SUM of that.

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

• 从这题的hint里才得知A_STRONGLY_AGREE一栏里的数值是百分比…
  6.Show the percentage of students who A_STRONGLY_AGREE to question 22 for the subject ‘(8) Computer Science’ show the same figure for the subject ‘(H) Creative Arts and Design’.

Use the ROUND function to show the percentage without decimal places.

SELECT subject,round(sum((A_STRONGLY_AGREE/100) //列表中A_STRONGLY_AGREE值为缺少百分号的百分比数，因此要用他除以100得到百分比（XX%）形式，再用结果乘总人数response得到A_STRONGLY_AGREE对应的人数。
*response)/sum(response)*100,0)
FROM nss
WHERE question='Q22'
AND subject in ('(8) Computer Science','(H) Creative Arts and Design')
group by subject

7.Show the average scores for question ‘Q22’ for each institution that include ‘Manchester’ in the name.

The column score is a percentage - you must use the method outlined above to multiply the percentage by the response and divide by the total response. Give your answer rounded to the nearest whole number.

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

8.Show the institution, the total sample size and the number of computing students for institutions in Manchester for ‘Q01’.

• 题目换句人话说就是：展示①曼切斯特地区的机构名称，②对应机构发出的问题01的样本量（问卷量），③这些样本中，和电脑computer相关的样本量

SELECT institution,sum(sample),sum(case when subject='(8) Computer Science' 
										then sample 
										else 0 end)
FROM nss
WHERE question='Q01'
AND (institution LIKE '%Manchester%')
group by institution

• 结合前几题的内容，得知答案第三列应该是subject和computer有关的对应的sample总量。用case when语句实现。