【SQL練習】【SQLZOO】 8+ Numeric Examples

8+ Numeric Examples

問題はこちらです。
NSS Tutorial - SQLZOO


以下が解答となります。

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

この例は、次のように応答した番号を示しています。
　・question 1
　・at 'Edinburgh Napier University'
　・studying '(8) Computer Science'
「STRONGLY AGREE」の割合を表示してください。

【解答】

SELECT A_STRONGLY_AGREE
  FROM nss
 WHERE question    = 'Q01'
   AND institution = 'Edinburgh Napier University'
   AND subject     = '(8) Computer Science'

2.Calculate how many agree or strongly agree

【問題】
Show the institution and subject where the score is at least 100 for question 15.

「question 15」の「score」が100以上の「institution」と「subject」を表示してください。

【解答】

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

3.Unhappy Computer Students

【問題】
Show the institution and score where the score for '(8) Computer Science' is less than 50 for question 'Q15'

「question 'Q15'」の「(8) Computer Science」の「score」が50未満である「institution」と「score」を表示してください。

【解答】

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

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

「(8) Computer Science'」と「(H) Creative Arts and Design」の各科目について、「question 22」に回答した「subject」と「total number of students（生徒の総数）」を表示してください。

【解答】

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

「(8) Computer Science」と「(H) Creative Arts and Design」の各科目について、「question 22」に「A_STRONGLY_AGREE」した「subject」と「total number of students（生徒の総数）」を表示してください。

【解答】

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

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

「(8) Computer Science」という主題について「question 22」に「A_STRONGLY_AGREE」した学生の割合、「(H) Creative Arts and Design」という主題についても同じ数値（※1）を表示してください。
※1：「A_STRONGLY_AGREE」した学生の割合

【解答】

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.Scores for Institutions in Manchester

【問題】
Show the average scores for question 'Q22' for each institution that include 'Manchester' in the name.

名前に「Manchester」が含まれている「each institution（各機関）」の「question 'Q22'」の「the average scores（平均スコア）」を表示してください。

【解答】

SELECT institution, 
       ROUND(SUM(response * score / 100) / SUM(response) * 100, 0)
  FROM nss
 WHERE question = 'Q22'
   AND institution LIKE '%Manchester%'
 GROUP BY institution;

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

「institution」、「the total sample size（サンプルサイズの合計）」、「’Q01’のマンチェスターの教育機関のコンピューティング学生の数（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;