# Difference between revisions of "NSS Tutorial"

Jump to: navigation, search

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)

## 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

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

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

```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)/SUM(response)
FROM nss
WHERE question='Q22'
AND subject IN ('(8) Computer Science','(H) Creative Arts and Design')
GROUP BY subject
```