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

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184
  1. -- in class examples
  2. -- dataset is employee(name)
  3. -- first, always see the data you are working with
  4. SELECT * FROM employee;
  5. -- then, see the size of the data
  6. SELECT COUNT(*) FROM employee;
  7. -- dataset is nobel(yr, subject, winner) (where winner is name)
  8. -- q1 show winners of the nobel prize in 1950
  9. SELECT yr, subject, winner FROM nobel
  10. WHERE yr = 1950;
  11. -- q2 show 1962 Literature prize winners
  12. SELECT winner
  13. FROM nobel
  14. WHERE yr = 1950winner, subject, year
  15. AND subject = 'Literature';
  16. -- q3 find nobel prizes that Albert Einstein won
  17. SELECT yr, subject
  18. FROM nobel
  19. WHERE winner = 'Albert Einstein';
  20. -- q4 Peace winners since 2000 inclusively
  21. SELECT winner
  22. FROM nobel
  23. WHERE yr >= 2000
  24. AND subject = 'Peace';
  25. -- Good idea to show the criteria you are filtering on
  26. SELECT winner, yr
  27. FROM nobel
  28. WHERE yr >= 2000
  29. AND subject = 'Peace';
  30. -- q5 Show all details (year, subject winner) of winners from 1980 to 1989 of he Literature prize
  31. SELECT *
  32. FROM nobel
  33. WHERE yr >= 1980
  34. AND yr <= 1989
  35. AND subject = 'Literature';
  36. -- use something other than two ands - LIKE works
  37. SELECT *
  38. FROM nobel
  39. WHERE yr LIKE '198%'
  40. AND subject = 'Literature';
  41. -- and we can also try this:
  42. SELECT *
  43. FROM nobel
  44. WHERE yr BETWEEN 1980 AND 1989 -- this is inclusively specified
  45. AND subject = 'Literature';
  46. -- q6 show the details of these winners
  47. SELECT *
  48. FROM nobel
  49. WHERE winner = 'Theodore Roosevelt'
  50. OR winner = 'Woodrow Wilson'
  51. OR winner = 'Jimmy Carter'
  52. OR winner = 'Barack Obama';
  53. -- better way, where winner IN (list of items)
  54. SELECT *
  55. FROM nobel
  56. WHERE winner IN ('Theodore Roosevelt', 'Woodrow Wilson', 'Jimmy Carter', 'Barack Obama');
  57. -- q7 Show the winners with the first name 'John'
  58. SELECT winner
  59. FROM nobel
  60. WHERE winner LIKE 'John %';
  61. -- q8 show the year, subject, and name of Physics winners for 1980 and the wnners of Chemistry for 1984
  62. SELECT *
  63. FROM nobel
  64. WHERE (subject = 'Physics' AND yr = 1980)
  65. OR (subject = 'Chemistry' AND yr = 1984)
  66. -- another one I came up with
  67. SELECT *
  68. FROM nobel
  69. WHERE yr = 1980
  70. AND subject = 'Physics';
  71. UNION
  72. SELECT *
  73. FROM nobel
  74. WHERE yr = 1984
  75. AND subject = 'Chemistry';
  76. -- q9 Show all information about 1980 excluding Chemistry and Medicine
  77. SELECT yr, subject, winner
  78. FROM nobel
  79. WHERE subject <> 'Chemistry' -- not equal is <>
  80. AND subject <> 'Medicine'
  81. AND yr = 1980;
  82. -- another one I came up with
  83. SELECT *
  84. FROM nobel
  85. WHERE yr = 1980 AND subject NOT IN ('Chemistry', 'Medicine');
  86. -- another one he did
  87. SELECT *
  88. FROM nobel
  89. WHERE subject NOT IN ('Chemistry', 'Medicine')
  90. AND yr = 1980;
  91. -- q10 Show the info for the 'Medicine' prize before 1910, and Literature after 2004 inclusively
  92. SELECT *
  93. FROM nobel
  94. WHERE (subject = 'Literature' AND yr >= 2004)
  95. OR (subject = 'Medicine' AND yr < 1910);
  96. -- q11 Find all of the prizes by Peter Grünberg
  97. SELECT *
  98. FROM nobel
  99. WHERE name = 'Peter Grünberg';
  100. -- he didn't expect this to work, lol. I think this newer database engine uses UTF-8 encoding for the data, and thus there is no problem there
  101. -- sad excuse for LIKE demonstrated here. He does mention it's bad to do this
  102. SELECT *
  103. FROM nobel
  104. WHERE name LIKE 'Peter Gr%nberg';
  105. -- can also use CHAR(252) to show ü. Not sure why you would need to do this since you can type this particular character, but I guess if your name has newlines in it that this would matter.
  106. SELECT *
  107. FROM nobel
  108. WHERE winner = 'Peter Gr' || CHAR(252) || 'nberg';
  109. -- q12 find this other guy
  110. SELECT *
  111. FROM nobel
  112. WHERE winner = 'Eugene O''Niell';
  113. -- q13 find winners that have names that start with 'Sir', and in the order name, year, subject
  114. SELECT winner, year, subject
  115. FROM nobel
  116. WHERE winner LIKE 'Sir %'
  117. ORDER BY yr DESC, winner;
  118. -- dataset is now world(name, continent, area, population, gdp)
  119. -- q1 list each country name where the population is larger than that of Russia
  120. SELECT name
  121. FROM world
  122. WHERE population > (
  123. SELECT population
  124. FROM world
  125. WHERE name = 'Russia'
  126. );
  127. -- note - you can only expect one result to be returned from the nested query, otherwise it will fail. For example, returning all countries numbers that have an R to start the name of the country will fail.
  128. -- could have also done the population straight.
  129. SELECT name
  130. FROM world
  131. WHERE population > 1460000000;
  132. -- "cheating"
  133. -- q2 show the countries with a per capita GDP greater than the UK
  134. SELECT name
  135. FROM world
  136. WHERE continent = 'Europe'
  137. AND gdp/population > (
  138. SELECT gdp/population AS percapitagdp
  139. FROM world
  140. WHERE name = 'United Kingdom'
  141. );
  142. -- q3 List the names and continent of countries in the continents containng either Argentena or Australia, and order by country
  143. SELECT name, continent
  144. FROM world
  145. WHERE continent IN (
  146. SELECT continent
  147. FROM world
  148. WHERE name IN ('Argentena', 'Australia')
  149. )
  150. ORDER BY name;