SHARE
TWEET

Untitled

a guest Nov 17th, 2019 73 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /* REEKS 7 */
  2.  
  3. /*
  4.   OEF6: alternatieve geskipt
  5. */
  6.  
  7. /* OEF 1 */
  8. SELECT c.NAME, c.NATION,
  9. (SELECT COUNT(1) FROM RANKING  WHERE c.cid = cid) "AANTRANKING",
  10. (SELECT COUNT(1) FROM RANKING r WHERE c.cid = cid and r.discipline='SL') "AANTSL"
  11. FROM COMPETITORS c
  12. WHERE (SELECT COUNT(1) FROM RANKING  WHERE c.cid = cid) >= 40
  13.   and (SELECT COUNT(1) FROM RANKING r WHERE c.cid = cid and r.discipline='SL') >= 10
  14. ORDER BY c.NATION, (SELECT COUNT(1) FROM RANKING  WHERE c.cid = cid) asc;
  15.  
  16. /* OEF 2 */
  17. SELECT  parent, name, capital, population,
  18. CAST(100.*population/(select SUM(population) from regios where r1.parent = parent) AS NUMERIC(5,2)) "t.o.v.regio"
  19. ,CAST(100.*population/(SELECT sum(population) from regios where parent like 'FR._') AS NUMERIC(5,2)) "t.o.v.land"
  20. ,CAST(area AS INT) area, elevation
  21. FROM    regios r1
  22. WHERE   parent LIKE 'FR._'
  23. ORDER BY parent, name;
  24.  
  25. /* OEF 3 */
  26. SELECT name,nation,finishaltitude from Resorts r
  27. where finishaltitude >= (select avg(finishaltitude) from resorts where r.nation=nation)
  28. order by nation;
  29.  
  30. /* OEF 4 */
  31. SELECT NAME, WEIGHT FROM COMPETITORS c
  32. WHERE (SELECT count(1) FROM COMPETITORS  WHERE weight >= c.weight and weight is not null and gender='M') <= 5
  33. and weight is not null and gender='M'
  34. ORDER BY weight desc;
  35.  
  36. SELECT c.NAME, c.WEIGHT from COMPETITORS c
  37. JOIN Competitors c1 on c.gender=c1.gender
  38. WHERE c.gender='M'
  39. GROUP BY c.name, c.weight
  40. HAVING sum(case when c.weight<=c1.weight then 1 end) <=5
  41. ORDER BY c.weight desc;
  42.  
  43. WITH x as (SELECT NAME, WEIGHT,
  44.            row_number() over(order by weight desc) nr
  45.            FROM Competitors
  46.            WHERE gender='M' and weight is not null )
  47. SELECT NAME, WEIGHT FROM x WHERE nr <= 5;
  48.  
  49. /* OEF 5 */
  50.  
  51. WITH x as
  52.       (SELECT   y.name continent ,count(distinct iso) aantal
  53.        FROM regios r
  54.               JOIN regios y on y.hasc=r.parent
  55.               JOIN taalgebruik g on g.hasc=r.hasc
  56.          GROUP BY y.name
  57.      )
  58. ,xx as
  59.      (SELECT continent,aantal
  60.           --,row_number() over(order by aantal) r1
  61.           --,row_number() over(order by aantal desc) r2
  62.       FROM x)
  63. SELECT m.continent,m.aantal
  64. FROM xx m
  65. WHERE aantal=(SELECT max(aantal) from xx) or aantal=(SELECT min(aantal) from xx);
  66.  
  67. /* OEF 6 */
  68. --toegelaten:union, aggregaat(sum,max), cte
  69. --geen: analystische(ranking,over) of join
  70.  
  71. WITH x as (
  72.             SELECT hasc1, hasc2, length FROM GRENZEN
  73.             WHERE hasc1 in (SELECT hasc from Regios where parent='EUR')
  74.               and hasc2 in (SELECT hasc from Regios where parent='EUR')
  75.           ),
  76.      y as (  --union
  77.             SELECT hasc1, hasc2, length from x
  78.             UNION ALL
  79.             SELECT hasc2, hasc1, length from x
  80.           ),
  81.      z as (
  82.             SELECT (SELECT name from Regios where hasc=hasc1) hasc1,
  83.                    (SELECT name from Regios where hasc=hasc2) hasc2,
  84.                    length,
  85.                    length - (select max(length) from y yy where yy.hasc1=y.hasc1 ) verschil
  86.             FROM y y
  87.           )
  88. select hasc1, hasc2, length, verschil
  89. from z z
  90. WHERE (SELECT count(1) from z zz where zz.length > z.length and z.hasc1=zz.hasc1) <= 2
  91. order by hasc1,verschil desc;          
  92.  
  93. /* OEF 7 */
  94. --startcode
  95. SELECT   taal, y.name continent, SUM(CAST(x.population * gebruik AS INT)) aantal
  96. FROM     taalgebruik g JOIN talen t  ON    g.iso = t.iso
  97.                        JOIN regios x ON   g.hasc = x.hasc
  98.                        JOIN regios y ON x.parent = y.hasc
  99. GROUP BY y.name, taal
  100. HAVING SUM(CAST(x.population * gebruik AS INT)) > 9999999
  101. ORDER BY 2, 3 DESC;
  102.  
  103. --met subqueries
  104. WITH x as (
  105.   SELECT  (SELECT t.taal from talen t where t.iso=g.iso) taal,
  106.           (SELECT y.name from regios y where y.hasc=(SELECT x.parent from regios x where x.hasc=g.hasc)) continent,
  107.           gebruik*(select r.population from regios r where r.hasc=g.hasc) aantal
  108.   FROM taalgebruik g
  109. )
  110. SELECT taal, continent, round(sum(aantal),0) aantal
  111. FROM x
  112. GROUP BY taal, continent --dont ask me why
  113. HAVING sum(aantal) > 9999999
  114. ORDER BY 2, 3 desc;
  115.  
  116. /* OEF 8 */
  117.  
  118. SELECT p.name
  119. FROM regios p
  120. JOIN regios q ON p.parent = q.hasc
  121. WHERE q.parent = 'BE'
  122. ORDER BY p.name;
  123.  
  124. --omvormen
  125. SELECT p.name,
  126.        (SELECT count(1) from regios k where k.parent=p.hasc) "#k",
  127.        (SELECT count(1) from regios kk where (/* TODO */ =p.hasc) "#kk"
  128. FROM regios p
  129. WHERE (SELECT parent from regios q where q.hasc=p.parent ) = 'BE'
  130. ORDER BY p.name;
  131.  
  132.  
  133. /* OEF 9 */
  134.  
  135.  
  136.  
  137. /* OEF 10 */
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top