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

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220
  1. -- aggregate functions
  2. -- COUNT()
  3. -- we're using the ZAGI database (and associated example commands)
  4. -- number of rows (* can also be replaced with a primary key?)
  5. COUNT(*)
  6. -- number of entries
  7. COUNT(field)
  8. -- ZAGI 13
  9. COUNT (*)
  10. FROM product;
  11. -- returns number or rows
  12. -- 15
  13. -- returns count, avg, min, max
  14. -- 16
  15. -- for each vendor id, get total number of product, and the average product price, sorted by vendor id.
  16. -- returns vid, count, avg
  17. -- GROUP BY xxxxx
  18. -- must have xxxx in the select statement, otherwise we can get errors
  19. -- 17 is highly un-recommended. it doesn't indicate what the data is sorted by (context)
  20. -- 18
  21. -- returns vid and count of products over 100$
  22. -- 19
  23. -- average product prices by category
  24. -- 20
  25. -- retunrs productid and the number of items sold
  26. -- SUM()
  27. -- shows a sum of the items
  28. -- 21
  29. -- shows number of transactions for each one, grouped by product
  30. -- HAVING
  31. -- similar to WHERE, used for a grouping feature, generally found after a GROUP BY statement
  32. -- enhanced query based on 28
  33. SELECT productid, productname, productprice, (
  34. SELECT AVG(productprice) FROM product
  35. ) AS avg_price
  36. FROM product
  37. WHERE productprice < (
  38. SELECT AVG (productprice)
  39. FROM product
  40. );
  41. -- notice that we can't just do WHERE productprice < avg_price. That's a bit derp. Some SQL db's can handle that, but ours (mySQL?) appears that it cannot
  42. -- switching to SQL zoo after q30
  43. -- dataset is now world(name, continent, area, population, gdp)
  44. -- q2 show the countries with a per capita GDP greater than the UK
  45. SELECT name
  46. FROM world
  47. WHERE continent = 'Europe'
  48. AND gdp/population > (
  49. SELECT gdp/population AS percapitagdp
  50. FROM world
  51. WHERE name = 'United Kingdom'
  52. );
  53. -- he did this first:
  54. SELECT gdp/population AS percapitagdp
  55. FROM world
  56. WHERE name = 'United Kingdom';
  57. -- q3 List the names and continent of countries in the continents containng either Argentena or Australia, and order by country
  58. SELECT name, continent
  59. FROM world
  60. WHERE continent IN (
  61. SELECT continent
  62. FROM world
  63. WHERE name IN ('Argentena', 'Australia')
  64. )
  65. ORDER BY name;
  66. -- he did this first
  67. SELECT continent
  68. FROM world
  69. WHERE name IN ('Argentena', 'Australia');
  70. -- q4 Which country has a population that is more than Canada and less than poland? Show the name and the population.
  71. SELECT name, country
  72. FROM world
  73. WHERE population > (
  74. SELECT population
  75. FROM world
  76. WHERE name = 'Canada'
  77. )
  78. AND population < (
  79. SELECT population
  80. FROM world
  81. WHERE name = 'Poland'
  82. );
  83. -- I came up with these first. He started with the external query first
  84. SELECT population
  85. FROM world
  86. WHERE name = 'Canada';
  87. SELECT population
  88. FROM world
  89. WHERE name = 'Poland';
  90. -- show the inner query results for more context as to how the numbers compares
  91. SELECT name, country, (
  92. SELECT population
  93. FROM world
  94. WHERE name = 'Canada'
  95. ) as popCanada, (
  96. SELECT population
  97. FROM world
  98. WHERE name = 'Poland'
  99. ) as popPoland
  100. FROM world
  101. WHERE population > (
  102. SELECT population
  103. FROM world
  104. WHERE name = 'Canada'
  105. )
  106. AND population < (
  107. SELECT population
  108. FROM world
  109. WHERE name = 'Poland'
  110. );
  111. -- q5 Germany, population 80 million has the largest population of the countries in Europe.
  112. -- Show the name and population of each country in Europe
  113. -- Show the population as a percentage of the population of Germany
  114. -- I made this first
  115. SELECT population
  116. FROM world
  117. WHERE name = 'Germany';
  118. -- first round
  119. SELECT name, population/(
  120. SELECT population
  121. FROM world
  122. WHERE name = 'Germany'
  123. ) AS popPctgOfGermany
  124. FROM world
  125. WHERE continent = 'Europe';
  126. -- second round - add rounding statement and multiply by 100 to get percentage
  127. SELECT name, ROUND (
  128. 100 * population/(
  129. SELECT population
  130. FROM world
  131. WHERE name = 'Germany'
  132. ), 0 -- number of decimal places
  133. ) AS popPctgOfGermany
  134. FROM world
  135. WHERE continent = 'Europe';
  136. -- third round - add percent symbol with || '%'
  137. SELECT name, ROUND (
  138. 100 * population/(
  139. SELECT population
  140. FROM world
  141. WHERE name = 'Germany'
  142. ), 0 -- number of decimal places
  143. ) || '%' AS popPctgOfGermany
  144. FROM world
  145. WHERE continent = 'Europe';
  146. -- q6 Which countries have a GDP greater than every contry in Europe (name only, some countries may have NULL GDP values)
  147. -- we need to find the greatest GDP in Europe as well
  148. SELECT name
  149. FROM world
  150. WHERE gdp > (
  151. SELECT MAX(gdp)
  152. FROM world
  153. WHERE continent = 'Europe'
  154. )
  155. --