【SQLZOO答え】８＋NSS Tutorial

SQL入門を勉強するため、友人にSQLの練習問題ないかと聞いたら、
【SQLZOO】というサイトを教えてもらいました。
ただし、問題を解いてるうちに、わからない問題に関して、クエリの答えがおらず、結果しか教えてくれないので、答えをアウトプットしようと思い、noteを始めました。　

０．SQLZOO練習問題

NSS Tutorial

１．Check out one row

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
 A_STRONGLY_AGREE
from nss
where
 question='Q01'
  and institution='Edinburgh Napier University'
  and subject='(8) Computer Science';

２．Calculate how many agree or strongly agree

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;

３．Unhappy Computer Students

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 subject='(8) Computer Science'
 and score < 50;

4．More Computing or Creative Students?

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='(8) Computer Science' 
 or subject= '(H) Creative Arts and Design')
group by
 subject;

５．Strongly Agree Numbers

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/100*response)
from
 nss
where
 question='Q22'
 and (subject='(8) Computer Science' 
 or subject= '(H) Creative Arts and Design')
group by
 subject;

６．Strongly Agree, Percentage

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*response)/sum(response)*100,0)
from
 nss
where
 question='Q22'
 and (subject='(8) Computer Science' 
 or subject= '(H) Creative Arts and Design')
group by
 subject;

７．Scores for Institutions in Manchester

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*response) /sum(response),0)
from
 nss
where
 question='Q22' 
 and institution like '%Manchester%'
group by
 institution;

８．Number of Computing Students in Manchester

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;

その他の答へ　　　　　　　　

０．SELECT basics
１．SELECT name
２．SELECT from World
３．SELECT from Nobel
４．SELECT within SELECT
５．SUM and COUNT
６．JOIN
７．More JOIN operations
８．Using Null
８+ Numeric Examples
９．Self join
１０．Tutorial Quizzes
１１．Tutorial Student Records
１２．Tutorial DDL

※問題を攻略でき次第、随時更新いたします。