Difference between revisions of "NSS Tutorial"

From SQLZOO
Jump to: navigation, search
(Created page with "==National Student Survey 2012== The National Student Survey http://www.thestudentsurvey.com/ is presented to thousands of graduating students in UK Higher Education. The surv...")
 
(→‎Scores for Institutions in Manchester)
(24 intermediate revisions by one user not shown)
Line 1: Line 1:
 +
<div class="ref_section">
 +
<table class='db_ref'>
 +
<tr><th>Field</th><th>Type</th></tr>
 +
<tr><td>ukprn</td><td>varchar(8)</td></tr>
 +
<tr><td>institution</td><td>varchar(100)</td></tr>
 +
<tr><td>subject</td><td>varchar(60)</td></tr>
 +
<tr><td>level</td><td>varchar(50)</td></tr>
 +
<tr><td>question</td><td>varchar(10)</td></tr>
 +
<tr><td>A_STRONGLY_DISAGREE</td><td>int(11)</td></tr>
 +
<tr><td>A_DISAGREE</td><td>int(11)</td></tr>
 +
<tr><td>A_NEUTRAL</td><td>int(11)</td></tr>
 +
<tr><td>A_AGREE</td><td>int(11)</td></tr>
 +
<tr><td>A_STRONGLY_AGREE</td><td>int(11)</td></tr>
 +
<tr><td>A_NA</td><td>int(11)</td></tr>
 +
<tr><td>CI_MIN</td><td>int(11)</td></tr>
 +
<tr><td>score</td><td>int(11)</td></tr>
 +
<tr><td>CI_MAX</td><td>int(11)</td></tr>
 +
<tr><td>response</td><td>int(11)</td></tr>
 +
<tr><td>sample</td><td>int(11)</td></tr>
 +
<tr><td>aggregate</td><td>char(1)</td></tr>
 +
</table>
 +
</div>
 +
 
==National Student Survey 2012==
 
==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 table <code>nss</code> has one row per institution, subject and question.
 
The table <code>nss</code> has one row per institution, subject and question.
 +
 +
==Check out one row==
 +
<div class='qu'>
 +
The example shows the number who responded for:
 +
*question 1
 +
*at 'Edinburgh Napier University'
 +
*studying '(8) Computer Science'
 +
<p class='imper'>Show the the percentage who STRONGLY AGREE</p>
 +
 +
<source lang='sql' class='def'>
 +
SELECT response
 +
  FROM nss
 +
WHERE question='Q01'
 +
  AND institution='Edinburgh Napier University'
 +
  AND subject='(8) Computer Science'
 +
</source>
 +
 +
<source lang='sql' class='ans'>
 +
SELECT A_STRONGLY_AGREE
 +
  FROM nss
 +
WHERE question='Q01'
 +
  AND institution='Edinburgh Napier University'
 +
  AND subject='(8) Computer Science'
 +
</source>
 +
</div>
 +
 +
==Calculate how many agree or strongly agree==
 +
<div class='qu'>
 +
<p class='imper'>Show the institution and subject where the '''score''' is at least 100 for question 15.
 +
</p>
 +
 +
<source lang='sql' class='def'>
 +
SELECT response
 +
  FROM nss
 +
WHERE question='Q01'
 +
  AND institution='Edinburgh Napier University'
 +
  AND subject='(8) Computer Science'
 +
</source>
 +
 +
<source lang='sql' class='ans'>
 +
SELECT institution, subject
 +
  FROM nss
 +
WHERE question='Q15'
 +
  AND score>=100
 +
</source>
 +
</div>
 +
 +
==Unhappy Computer Students==
 +
<div class='qu'>
 +
<p class='imper'>Show the institution and score where the score for '(8) Computer Science' is less than 50 for question 'Q15'
 +
</p>
 +
 +
<source lang='sql' class='def'>
 +
SELECT institution,score
 +
  FROM nss
 +
WHERE question='Q15'
 +
  AND institution='Edinburgh Napier University'
 +
  AND subject='(8) Computer Science'
 +
</source>
 +
 +
<source lang='sql' class='ans'>
 +
SELECT institution, score
 +
  FROM nss
 +
WHERE question='Q15'
 +
  AND score<50
 +
  AND subject = '(8) Computer Science'
 +
</source>
 +
</div>
 +
 +
==More Computing or Creative Students?==
 +
<div class='qu'>
 +
<p class='imper'>Show the total number of students who responded to question 22 for the subject '(8) Computer Science' show the same figure for the subject '(H) Creative Arts and Design'.
 +
</p>
 +
 +
<div class='hint' title='HINT'>
 +
You will need to use SUM over the '''response''' column and GROUP BY '''subject'''
 +
</div>
 +
 +
<source lang='sql' class='def'>
 +
SELECT institution,score
 +
  FROM nss
 +
WHERE question='Q15'
 +
  AND institution='Edinburgh Napier University'
 +
  AND subject='(8) Computer Science'
 +
</source>
 +
 +
<source lang='sql' class='ans'>
 +
SELECT subject,SUM(response)
 +
  FROM nss
 +
WHERE question='Q22'
 +
  AND subject IN ('(8) Computer Science','(H) Creative Arts and Design')
 +
GROUP BY subject
 +
</source>
 +
</div>
 +
 +
==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'.
 +
</p>
 +
 +
<div class='hint' title='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.
 +
</div>
 +
 +
<source lang='sql' class='def'>
 +
SELECT institution,score
 +
  FROM nss
 +
WHERE question='Q15'
 +
  AND institution='Edinburgh Napier University'
 +
  AND subject='(8) Computer Science'
 +
</source>
 +
 +
<source lang='sql' class='ans'>
 +
SELECT subject,SUM(response*A_STRONGLY_AGREE/100)
 +
  FROM nss
 +
WHERE question='Q22'
 +
  AND subject IN ('(8) Computer Science','(H) Creative Arts and Design')
 +
GROUP BY subject
 +
</source>
 +
</div>
 +
 +
==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'.
 +
</p>
 +
Use the '''ROUND''' function to show the percentage without decimal places.
 +
 +
<source lang='sql' class='def'>
 +
SELECT institution,score
 +
  FROM nss
 +
WHERE question='Q15'
 +
  AND institution='Edinburgh Napier University'
 +
  AND subject='(8) Computer Science'
 +
</source>
 +
 +
<source lang='sql' class='ans'>
 +
SELECT subject,
 +
  ROUND(SUM(response*A_STRONGLY_AGREE)/SUM(response),0)
 +
  FROM nss
 +
WHERE question='Q22'
 +
  AND subject IN ('(8) Computer Science','(H) Creative Arts and Design')
 +
GROUP BY subject
 +
</source>
 +
</div>
 +
 +
 +
==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.
 +
</p>
 +
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'>
 +
SELECT institution,subject,score,response
 +
  FROM nss
 +
WHERE question='Q22'
 +
  AND (institution LIKE '%Manchester%')
 +
ORDER BY institution
 +
</source>
 +
 +
<source lang='sql' class='ans'>
 +
SELECT institution,
 +
  ROUND(SUM(response*score)/SUM(response),0) score
 +
  FROM nss
 +
WHERE question='Q22'
 +
  AND (institution LIKE '%Manchester%')
 +
GROUP BY institution
 +
</source>
 +
</div>
 +
 +
==Number of Computing Students in Manchester==
 +
<div class='qu'>
 +
<p class='imper'>Show the institution, the total sample size and the number of computing students for institutions in Manchester for 'Q01'.
 +
</p>
 +
 +
<source lang='sql' class='def'>
 +
SELECT institution,subject,score,response
 +
  FROM nss
 +
WHERE question='Q22'
 +
  AND (institution LIKE '%Manchester%')
 +
</source>
 +
 +
<source lang='sql' class='ans'>
 +
SELECT institution,
 +
  SUM(sample),
 +
  SUM(CASE WHEN subject LIKE '(8)%' THEN sample END) comp
 +
  FROM nss
 +
WHERE question='Q01'
 +
  AND (institution LIKE '%Manchester%')
 +
GROUP BY institution
 +
</source>
 +
</div>

Revision as of 16:18, 10 December 2012

FieldType
ukprnvarchar(8)
institutionvarchar(100)
subjectvarchar(60)
levelvarchar(50)
questionvarchar(10)
A_STRONGLY_DISAGREEint(11)
A_DISAGREEint(11)
A_NEUTRALint(11)
A_AGREEint(11)
A_STRONGLY_AGREEint(11)
A_NAint(11)
CI_MINint(11)
scoreint(11)
CI_MAXint(11)
responseint(11)
sampleint(11)
aggregatechar(1)

Contents

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 table nss has one row per institution, subject and question.

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 response
  FROM nss
 WHERE question='Q01'
   AND institution='Edinburgh Napier University'
   AND subject='(8) Computer Science'
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 response
  FROM nss
 WHERE question='Q01'
   AND institution='Edinburgh Napier University'
   AND subject='(8) Computer Science'
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 institution='Edinburgh Napier University'
   AND subject='(8) Computer Science'
SELECT institution, score
  FROM nss
 WHERE question='Q15'
   AND score<50
   AND subject = '(8) Computer Science'

More Computing or Creative Students?

Show the total number of students who responded to question 22 for the subject '(8) Computer Science' show the same figure for the subject '(H) Creative Arts and Design'.

You will need to use SUM over the response column and GROUP BY subject 

SELECT institution,score
  FROM nss
 WHERE question='Q15'
   AND institution='Edinburgh Napier University'
   AND subject='(8) Computer Science'
SELECT subject,SUM(response)
  FROM nss
 WHERE question='Q22'
   AND subject IN ('(8) Computer Science','(H) Creative Arts and Design')
GROUP BY subject

Strongly Agree Numbers

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

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 institution,score
  FROM nss
 WHERE question='Q15'
   AND institution='Edinburgh Napier University'
   AND subject='(8) Computer Science'
SELECT subject,SUM(response*A_STRONGLY_AGREE/100)
  FROM nss
 WHERE question='Q22'
   AND subject IN ('(8) Computer Science','(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 institution,score
  FROM nss
 WHERE question='Q15'
   AND institution='Edinburgh Napier University'
   AND subject='(8) Computer Science'
SELECT subject,
  ROUND(SUM(response*A_STRONGLY_AGREE)/SUM(response),0)
  FROM nss
 WHERE question='Q22'
   AND subject IN ('(8) Computer Science','(H) Creative Arts and Design')
GROUP BY subject


Scores for Institutions in Manchester

Show the average scores for question 'Q01' 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,subject,score,response
  FROM nss
 WHERE question='Q22'
   AND (institution LIKE '%Manchester%')
ORDER BY institution
SELECT institution,
  ROUND(SUM(response*score)/SUM(response),0) score
  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,subject,score,response
  FROM nss
 WHERE question='Q22'
   AND (institution LIKE '%Manchester%')
SELECT institution,
  SUM(sample),
  SUM(CASE WHEN subject LIKE '(8)%' THEN sample END) comp
  FROM nss
 WHERE question='Q01'
   AND (institution LIKE '%Manchester%')
GROUP BY institution
Retrieved from "http://www.sqlzoo.net/w/index.php?title=NSS_Tutorial&oldid=5276"
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense