NSS Tutorial - Revision history

216.239.55.193: clarified data in student survey tables

2013-04-01T03:59:09Z

clarified data in student survey tables

2012-12-10T16:18:07Z

‎Scores for Institutions in Manchester

2012-12-10T16:12:29Z

‎Scores for Institutions in London

2012-12-10T16:12:19Z

‎Scores for Institutions in London

2012-12-10T16:05:02Z

‎Show scores by subject

2012-12-10T16:03:52Z

‎Strongly Agree, Percentage

2012-12-10T15:58:34Z

‎Strongly Agree, Percentage

2012-12-10T15:56:29Z

‎Strongly Agree

2012-12-10T15:56:16Z

‎Strongly Agree

2012-12-10T15:51:19Z

‎More Computing or Creative Students?

@@ Line 23: / Line 23: @@
 ==National Student Survey 2012==
-The National Student Survey http://www.thestudentsurvey.com/ is presented to thousands of graduating students in UK Higher Education. The survey asks 22 questions, students can respond with STRONGLY DISAGREE, DISAGREE, NEUTRAL, AGREE or STRONGLY AGREE.
+The National Student Survey http://www.thestudentsurvey.com/ is presented to thousands of graduating students in UK Higher Education. The survey asks 22 questions, students can respond with STRONGLY DISAGREE, DISAGREE, NEUTRAL, AGREE or STRONGLY AGREE.  The values in these columns represent PERCENTAGES of the total students who responded with that answer.
 The table <code>nss</code> has one row per institution, subject and question.

@@ Line 179: / Line 179: @@
 <source lang='sql' class='def'>
-SELECT institution,score
+SELECT institution,subject,score,response
    FROM nss
-  WHERE question='Q15'
+  WHERE question='Q22'
-    AND institution='Edinburgh Napier University'
+    AND (institution LIKE '%Manchester%')
-   AND subject='(8) Computer Science'
+ORDER BY institution
 </source>
@@ Line 191: / Line 191: @@
    FROM nss
   WHERE question='Q22'
     AND (institution LIKE '%Manchester%')
 GROUP BY institution
 </source>
 </div>

@@ Line 172: / Line 172: @@
-==Scores for Institutions in London==
+==Scores for Institutions in Manchester==
 <div class='qu'>
 <p class='imper'>Show the average scores for question 'Q01' for each institution that include 'Manchester' in the name.

@@ Line 174: / Line 174: @@
 ==Scores for Institutions in London==
 <div class='qu'>
-<p class='imper'>Show the average scores for question 'Q01' for each institution that include "London" in the name.
+<p class='imper'>Show the average scores for question 'Q01' for each institution that include 'Manchester' in the name.
 </p>
-The column '''score''' is a percentage - you must use the method outlined
+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.
 <source lang='sql' class='def'>
@@ Line 188: / Line 188: @@
 <source lang='sql' class='ans'>
 SELECT institution,
-   ROUND(SUM(response*score)/SUM(response),0)
+   ROUND(SUM(response*score)/SUM(response),0) score
    FROM nss
-  WHERE question='Q01'
+  WHERE question='Q22'
-    AND institution LIKE '%London%'
+    AND (institution LIKE '%Manchester%')
 GROUP BY institution
 </source>
 </div>

@@ Line 172: / Line 172: @@
-==Show scores by subject==
+==Scores for Institutions in London==
 <div class='qu'>
 <p class='imper'>Show the average scores for question 'Q01' for each institution that include "London" in the name.

@@ Line 168: / Line 168: @@
     AND subject IN ('(8) Computer Science','(H) Creative Arts and Design')
 GROUP BY subject
 </source>
 </div>

@@ Line 151: / Line 151: @@
 <p class='imper'>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'.
 </p>
 <source lang='sql' class='def'>
@@ Line 161: / Line 162: @@
 <source lang='sql' class='ans'>
-SELECT subject,SUM(response*A_STRONGLY_AGREE)/SUM(response)
+SELECT subject,
    FROM nss
   WHERE question='Q22'

@@ Line 147: / Line 147: @@
 </div>
-==Strongly Agree==
+==Strongly Agree, Percentage==
 <div class='qu'>
 <p class='imper'>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'.

@@ Line 121: / Line 121: @@
 </div>
-==Strongly Agree==
+==Strongly Agree Numbers==
 <div class='qu'>
 <p class='imper'>Show the total number 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'.

@@ Line 101: / Line 101: @@
 <div class='hint' title='HINT'>
-You will need to use SUM and GROUP BY.
+You will need to use SUM over the '''response''' column and GROUP BY '''subject'''
 </div>
@@ Line 113: / Line 113: @@
 <source lang='sql' class='ans'>
-SELECT subject,SUM(score)
+SELECT subject,SUM(response)
    FROM nss
   WHERE question='Q22'
@@ Line 120: / Line 120: @@
 </source>
 </div>
 ==Strongly Agree==