SQL Zoo 8+.NSS Tutorial

以下数据来自SQL Zoo

1.at 'Edinburgh Napier University'，studying '(8) Computer Science'，Show the the percentage who STRONGLY AGREE.（在爱丁堡纳皮尔大学，学习“计算机科学”，显示STRONGLY AGREE的百分比）

SELECT A_STRONGLY_AGREEFROM nssWHERE 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.（列出第15题得分至少为100分的院校和科目）

SELECT institution,subjectFROM nssWHERE 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'(在“Q15”题中，显示“(8)计算机科学”得分低于50分的院校和分数)

SELECT institution,scoreFROM nssWHERE question='Q15'AND score <50AND subject='(8) Computer Science'

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'.(请列出在“(8)计算机科学”和“(H)创意艺术与设计”两个科目中回答问题22的学生总数)

SELECT subject,sum(response)FROM nssWHERE 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'.(请列出在“(8)计算机科学”和“(H)创意艺术与设计”两个科目中强烈同意问题22的科目和学生总数)

SELECT subject,sum(response*A_STRONGLY_AGREE)/100FROM nssWHERE 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'.(显示在“(8)计算机科学”科目中强烈同意问题22的学生的百分比，在“(H)创意艺术与设计”科目中显示相同的数字)

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

7.Show the average scores for question 'Q22' for each institution that include 'Manchester' in the name.(显示名称中包含“曼彻斯特”的每所院校在“Q22”问题上的平均得分)

SELECT institution,round(sum(score*response)/sum(response))FROM nssWHERE question='Q22'AND (institution LIKE '%Manchester%')
group BY institution
order 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 NULL END)FROM nssWHERE question='Q01'AND (institution LIKE '%Manchester%')
group by institution