sql playground
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

11-12-18.sql 4.8KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134
  1. -- ukprn varchar(8)
  2. -- institution varchar(100)
  3. -- subject varchar(60)
  4. -- level varchar(50)
  5. -- question varchar(10)
  6. -- A_STRONGLY_DISAGREE int(11)
  7. -- A_DISAGREE int(11)
  8. -- A_NEUTRAL int(11)
  9. -- A_AGREE int(11)
  10. -- A_STRONGLY_AGREE int(11)
  11. -- A_NA int(11)
  12. -- CI_MIN int(11)
  13. -- score int(11)
  14. -- CI_MAX int(11)
  15. -- response int(11)
  16. -- sample int(11)
  17. -- aggregate char(1))
  18. -- 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.
  19. -- Q1 prep
  20. SELECT COUNT(*), COUNT(DISTINCT ukprn), COUNT(DISTINCT institution), COUNT(DISTINCT subject), COUNT(DISTINCT level), COUNT(DISTINCT question)
  21. FROM nss;
  22. -- 50946 288 288 21 2 28
  23. -- "let's see if this will work"
  24. SELECT COUNT(*), COUNT(DISTINCT ukprn, subject), COUNT(DISTINCT institution), COUNT(DISTINCT subject), COUNT(DISTINCT level), COUNT(DISTINCT question)
  25. FROM nss;
  26. -- 50946 2055 288 21 2 28
  27. -- "add a question..."
  28. SELECT COUNT(*), COUNT(DISTINCT ukprn, subject, question), COUNT(DISTINCT institution), COUNT(DISTINCT subject), COUNT(DISTINCT level), COUNT(DISTINCT question)
  29. FROM nss;
  30. -- 50946 45726 288 21 2 28
  31. -- level is a reserved word
  32. SELECT COUNT(*), COUNT(DISTINCT ukprn, subject, question, level), COUNT(DISTINCT institution), COUNT(DISTINCT subject), COUNT(DISTINCT level), COUNT(DISTINCT question)
  33. FROM nss;
  34. -- 50946 50946 288 21 2 28
  35. -- finally, we find a suitable primary key (notice the first and second answer are the same now)
  36. -- it's good to know your data - either ask the designer, or make some queries on how the data is structured
  37. SELECT AVG(score), MAX(score), MIN(score), AVG(CI_MIN), MAX(CI_MIN), MIN(CI_MIN), AVG(CI_MAX), MAX(CI_MAX), MIN(CI_MAX)
  38. FROM nss;
  39. -- score
  40. -- 78.6771 100 5
  41. -- CI_MIN
  42. -- 66.1851 97 1
  43. -- CI_MAX
  44. -- 86.7345 100 16
  45. -- seems like normalized data, nothing that isn't inbetween 0 and 100
  46. -- q1: Show the the percentage who STRONGLY AGREE for Question 1 at 'Edinburgh Napier University' studying '(8) Computer Science'
  47. SELECT A_STRONGLY_AGREE
  48. FROM nss
  49. WHERE question = 'Q01'
  50. AND institution = 'Edinburgh Napier University'
  51. AND subject = '(8) Computer Science';
  52. -- 23
  53. -- q2: Show the institution and subject where the score is at least 100 for question 15
  54. SELECT institution, subject
  55. FROM nss
  56. WHERE score = 100
  57. AND question = 'Q15';
  58. -- q3: Show the institution and score where the score for '(8) Computer Science' is less than 50 for question 'Q15'
  59. SELECT institution, score
  60. FROM nss
  61. WHERE score < 50
  62. AND subject = '(8) Computer Science'
  63. AND question = 'Q15';
  64. -- q4: 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'.
  65. SELECT subject, SUM(response)
  66. FROM nss
  67. WHERE question = 'Q22'
  68. AND subject IN ('(8) Computer Science', '(H) Creative Arts and Design')
  69. GROUP BY subject;
  70. -- (8) Computer Science 10612
  71. -- (H) Creative Arts and Design 34370
  72. -- q5: Show the subject and total number of studens who A_STRONGLY_AGREE to question 22 for each of the subjects '(8) Computer Science' and '(H) Creative Arts and Design'
  73. SELECT subject, SUM(A_STRONGLY_AGREE * response / 100)
  74. FROM nss
  75. WHERE question = 'Q22'
  76. AND subject IN ('(8) Computer Science', '(H) Creative Arts and Design')
  77. GROUP BY subject;
  78. -- (8) Computer Science 3563.5600
  79. -- (H) Creative Arts and Design 12484.0100
  80. -- q6: Show the percentage of students who A_STRONGLY_AGREE to question 22 for the subject '(8) Computer Science', and show the same figure for '(H) Creative Arts and Design'
  81. SELECT subject, ROUND(SUM(A_STRONGLY_AGREE * response)/SUM(response))
  82. FROM nss
  83. WHERE question = 'Q22'
  84. AND subject IN ('(8) Computer Science', '(H) Creative Arts and Design')
  85. GROUP BY subject;
  86. -- compare to inaccurate average
  87. SELECT subject, ROUND(SUM(A_STRONGLY_AGREE * response)/SUM(response)) AS pctg_STRONGLY_AGREE, ROUND(AVG(A_STRONGLY_AGREE)) AS non_weighted
  88. FROM nss
  89. WHERE question = 'Q22'
  90. AND subject IN ('(8) Computer Science', '(H) Creative Arts and Design')
  91. GROUP BY subject;
  92. -- this stuff below I was just working on bcause can.
  93. -- q7: Show the average scores for question 'Q22' for each institution that includes 'Manchester' in the name
  94. SELECT institution, ROUND(AVG(score))
  95. FROM nss
  96. WHERE question='Q22'
  97. AND (institution LIKE '%Manchester%')
  98. GROUP BY institution
  99. -- this doesn't work yet
  100. -- q8: Show the institution, sample size, and number of computing students for instititions in Manchester for 'Q01'
  101. SELECT institution, SUM(sample), SUM(response)
  102. FROM nss
  103. WHERE question='Q01'
  104. AND (institution LIKE '%Manchester%')
  105. AND subject = '(8) Computer Science'
  106. GROUP BY institution
  107. -- this doesn't work yet
  108. --