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-14-18.sql 4.0KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164
  1. -- continuing on previous database
  2. -- some random thing to start class
  3. SELECT question, COUNT(*), AVG(score)
  4. FROM nss
  5. GROUP BY question
  6. ORDER BY AVG(score);
  7. -- "explanatory factors"
  8. -- runs that and avg score together
  9. SELECT question, COUNT(*), AVG(score)
  10. FROM nss;
  11. SELECT question, COUNT(*), AVG(score)
  12. FROM nss
  13. GROUP BY question
  14. ORDER BY AVG(score);
  15. -- close but not exact. this is the "real average"
  16. SELECT question, COUNT(*), AVG(score), SUM(score * response) / SUM(response)
  17. FROM nss
  18. GROUP BY question
  19. ORDER BY AVG(score);
  20. -- some stuff for institution
  21. SELECT institution, COUNT(*), AVG(score), SUM(score * response) / SUM(response) as real_avg
  22. FROM nss
  23. GROUP BY institution;
  24. -- some stuff by level
  25. SELECT level, COUNT(*), AVG(score), SUM(score * response) / SUM(response) as real_avg
  26. FROM nss
  27. GROUP BY level
  28. ORDER BY AVG(score);
  29. -- lots more stuff
  30. SELECT institution, question, COUNT(*), AVG(score), SUM(score * response) / SUM(response) as real_avg
  31. FROM nss
  32. GROUP BY institution, question
  33. ORDER BY AVG(score) DESC;
  34. SELECT institution, subject, COUNT(*), AVG(score), SUM(score * response) / SUM(response) as real_avg
  35. FROM nss
  36. GROUP BY institution, subject
  37. ORDER BY AVG(score) DESC;
  38. -- 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'
  39. SELECT subject, ROUND(SUM(A_STRONGLY_AGREE * response)/SUM(response),0) AS pct_STRONGLY_AGREE, ROUND(AVG(A_STRONGLY_AGREE),0) as non_weighted
  40. FROM nss
  41. WHERE question = 'Q22'
  42. AND subject IN ('(8) Computer Science', '(H) Creative Arts and Design')
  43. GROUP BY subject;
  44. -- q7: Show the average scores for question 'Q22' for each institution that includes 'Manchester' in the name
  45. SELECT institution, ROUND(SUM(score * response)/SUM(response)) as real_avg
  46. FROM nss
  47. WHERE question='Q22'
  48. AND (institution LIKE '%Manchester%')
  49. GROUP BY institution;
  50. -- q8: Show the institution, sample size, and number of computing students for instititions in Manchester for 'Q01'
  51. -- first part
  52. SELECT institution, SUM(sample)
  53. FROM nss
  54. WHERE question='Q01'
  55. AND (institution LIKE '%Manchester%')
  56. GROUP BY institution;
  57. -- now just CS
  58. SELECT institution, SUM(sample) AS comp
  59. FROM nss
  60. WHERE question='Q01'
  61. AND (institution LIKE '%Manchester%')
  62. AND subject = '(8) Computer Science'
  63. GROUP BY institution;
  64. -- alright, the answer wants both. Now what?
  65. -- fact: this is not possible with one query, must connect two queries
  66. -- diverges to talk about homework.
  67. -- q5.1a
  68. CREATE VIEW e51a AS
  69. SELECT vendorID, vendorname
  70. FROM vendor;
  71. -- These are from the book.
  72. -- Best to test and then save as view
  73. -- q5.1c
  74. CREATE VIEW e51c AS
  75. SELECT productid, productname, productprice
  76. FROM product
  77. WHERE productprice >= 100;
  78. -- q5.1 ?
  79. -- display pid and pname of the cheapest product
  80. -- find price of cheapest product
  81. -- find that product by price and show the information about it
  82. SELECT productid, productname
  83. FROM product
  84. WHERE productprice = (
  85. SELECT MIN(productprice)
  86. FROM product
  87. );
  88. -- q5.1m
  89. -- find the average price
  90. -- show products with price below average
  91. CREATE VIEW e51m AS
  92. SELECT productid, productname, productprice, vendorid
  93. FROM product
  94. WHERE productprice < (
  95. SELECT AVG(productprice)
  96. FROM product
  97. );
  98. -- q5.1n
  99. -- show the ID of the product that has been sold in the highest quantity
  100. SELECT productid, noofitems
  101. FROM soldvia
  102. WHERE noofitems = (
  103. SELECT MAX(noofitems)
  104. FROM soldvia
  105. );
  106. -- note - this only finds one thing
  107. -- find total of each product
  108. SELECT productid, SUM(noofitems)
  109. FROM soldvia
  110. GROUP BY productid;
  111. -- find maximum quantity
  112. SELECT MAX(noofitems)
  113. FROM soldvia;
  114. -- his example
  115. SELECT MAX(t.sumquant)
  116. FROM (
  117. SELECT productid, SUM(noofitems) AS sumquant
  118. FROM soldvia
  119. GROUP BY productid
  120. ) AS t;
  121. -- all in one query
  122. -- CREATE VIEW e51n AS -- aparently you can't create a subview here?
  123. SELECT productid, SUM(noofitems)
  124. FROM soldvia
  125. GROUP BY productid
  126. HAVING SUM(noofitems) = (
  127. SELECT MAX(t.sumquant)
  128. FROM (
  129. SELECT productid, SUM(noofitems) AS sumquant
  130. FROM soldvia
  131. GROUP BY productid
  132. ) AS t
  133. );