/* REEKS 7 */

/*
  OEF6: alternatieve geskipt
*/

/* OEF 1 */
SELECT c.NAME, c.NATION,
(SELECT COUNT(1) FROM RANKING  WHERE c.cid = cid) "AANTRANKING",
(SELECT COUNT(1) FROM RANKING r WHERE c.cid = cid and r.discipline='SL') "AANTSL"
FROM COMPETITORS c
WHERE (SELECT COUNT(1) FROM RANKING  WHERE c.cid = cid) >= 40
  and (SELECT COUNT(1) FROM RANKING r WHERE c.cid = cid and r.discipline='SL') >= 10
ORDER BY c.NATION, (SELECT COUNT(1) FROM RANKING  WHERE c.cid = cid) asc;

/* OEF 2 */
SELECT  parent, name, capital, population,
CAST(100.*population/(select SUM(population) from regios where r1.parent = parent) AS NUMERIC(5,2)) "t.o.v.regio"
,CAST(100.*population/(SELECT sum(population) from regios where parent like 'FR._') AS NUMERIC(5,2)) "t.o.v.land"
,CAST(area AS INT) area, elevation
FROM    regios r1
WHERE   parent LIKE 'FR._'
ORDER BY parent, name;

/* OEF 3 */
SELECT name,nation,finishaltitude from Resorts r
where finishaltitude >= (select avg(finishaltitude) from resorts where r.nation=nation)
order by nation;

/* OEF 4 */
SELECT NAME, WEIGHT FROM COMPETITORS c
WHERE (SELECT count(1) FROM COMPETITORS  WHERE weight >= c.weight and weight is not null and gender='M') <= 5
and weight is not null and gender='M'
ORDER BY weight desc;

SELECT c.NAME, c.WEIGHT from COMPETITORS c
JOIN Competitors c1 on c.gender=c1.gender
WHERE c.gender='M'
GROUP BY c.name, c.weight
HAVING sum(case when c.weight<=c1.weight then 1 end) <=5
ORDER BY c.weight desc;

WITH x as (SELECT NAME, WEIGHT,
           row_number() over(order by weight desc) nr
           FROM Competitors
           WHERE gender='M' and weight is not null )
SELECT NAME, WEIGHT FROM x WHERE nr <= 5;

/* OEF 5 */

WITH x as
      (SELECT   y.name continent ,count(distinct iso) aantal
       FROM regios r
              JOIN regios y on y.hasc=r.parent
              JOIN taalgebruik g on g.hasc=r.hasc
         GROUP BY y.name
     )
,xx as
     (SELECT continent,aantal
          --,row_number() over(order by aantal) r1
          --,row_number() over(order by aantal desc) r2
      FROM x)
SELECT m.continent,m.aantal
FROM xx m
WHERE aantal=(SELECT max(aantal) from xx) or aantal=(SELECT min(aantal) from xx);

/* OEF 6 */
--toegelaten:union, aggregaat(sum,max), cte
--geen: analystische(ranking,over) of join

WITH x as (
            SELECT hasc1, hasc2, length FROM GRENZEN
            WHERE hasc1 in (SELECT hasc from Regios where parent='EUR')
              and hasc2 in (SELECT hasc from Regios where parent='EUR')
          ),
     y as (  --union
            SELECT hasc1, hasc2, length from x
            UNION ALL
            SELECT hasc2, hasc1, length from x
          ),
     z as (
            SELECT (SELECT name from Regios where hasc=hasc1) hasc1,
                   (SELECT name from Regios where hasc=hasc2) hasc2,
                   length,
                   length - (select max(length) from y yy where yy.hasc1=y.hasc1 ) verschil
            FROM y y
          )
select hasc1, hasc2, length, verschil
from z z
WHERE (SELECT count(1) from z zz where zz.length > z.length and z.hasc1=zz.hasc1) <= 2
order by hasc1,verschil desc;

/* OEF 7 */
--startcode
SELECT   taal, y.name continent, SUM(CAST(x.population * gebruik AS INT)) aantal
FROM     taalgebruik g JOIN talen t  ON    g.iso = t.iso
                       JOIN regios x ON   g.hasc = x.hasc
                       JOIN regios y ON x.parent = y.hasc
GROUP BY y.name, taal
HAVING SUM(CAST(x.population * gebruik AS INT)) > 9999999
ORDER BY 2, 3 DESC;

--met subqueries
WITH x as (
  SELECT  (SELECT t.taal from talen t where t.iso=g.iso) taal,
          (SELECT y.name from regios y where y.hasc=(SELECT x.parent from regios x where x.hasc=g.hasc)) continent,
          gebruik*(select r.population from regios r where r.hasc=g.hasc) aantal
  FROM taalgebruik g
)
SELECT taal, continent, round(sum(aantal),0) aantal
FROM x
GROUP BY taal, continent --dont ask me why
HAVING sum(aantal) > 9999999
ORDER BY 2, 3 desc;

/* OEF 8 */

SELECT p.name
FROM regios p
JOIN regios q ON p.parent = q.hasc
WHERE q.parent = 'BE'
ORDER BY p.name;

--omvormen
SELECT p.name,
       (SELECT count(1) from regios k where k.parent=p.hasc) "#k",
       (SELECT count(1) from regios kk where (/* TODO */ =p.hasc) "#kk"
FROM regios p
WHERE (SELECT parent from regios q where q.hasc=p.parent ) = 'BE'
ORDER BY p.name;


/* OEF 9 */



/* OEF 10 */