/* REEKS 7 */

/*
  ...
*/

/* 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;

/* OEF 5 */

/* OEF 6 */

/* OEF 7 */

/* OEF 8 */

/* OEF 9 */

/* OEF 10 */