– 第8+关 Numeric Examples NSS Tutorial - SQLZOO

--1.Show the the percentage who STRONGLY AGREE
-- 熟悉表格nss 

select A_STRONGLY_AGREE
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.
-- 练习 where 
select institution,subject 
from nss 
where score >= 100 
and question ='Q15'

-- 3. Show the institution and score where the score for '(8) Computer Science' is less than 50 for question 'Q15'
-- 练习 where 
-- less than 是< ,不是<=

select institution,score 
from nss 
where score < 50
and subject = '(8) Computer Science'
and question = 'Q15'

-- 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'.
-- 练习where 
-- 注意 and 和 or， or之间要加括号。

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'.
-- 练习 sum
-- 注意 A_STRONGLY_AGREE是比例，但没有%，需要除以100，变成分数。

select subject, sum(A_STRONGLY_AGREE*response/100)
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'.
-- 练习rooun, sum 
-- 注意，是求所有强烈同意的同学占所有学生的比例。

select subject, round(sum(A_STRONGLY_AGREE*response/100)/sum(response),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.
-- 练习 round ,sum ,like 
select institution,round((sum(score*response/100)/sum(response))*100,0) as score
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), 
(select sample 
from nss y 
where subject = '(8) Computer Science'
and x.institution = y.institution
and question = 'Q01') as comp 
from nss x
where question = 'Q01'
and institution like '%Manchester%'
group by institution