SQLZOO: Scottish Parliament/zh & NSS Tutorial

4. 列出政黨名單，當中最少有一名黨員在議會內。

select party.name
from msp join party on msp.party=party.code
group by party.name

因为 msp 是按党员统计的表，所以直接将 msp 和 party 内联接，返回的一定是有党员的政党

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'.

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. 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'.

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

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'.

SELECT subject,round(sum(response*A_STRONGLY_AGREE/100)/sum(response)*100,0)
FROM nss
WHERE question='Q22'
AND (subject='(8) Computer Science'
OR subject= '(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.

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'.

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