oplossingen

OPGAVE
Geef voor alle huidige bestuurleden hun functie en de lijst van boetes die voor hen werd betaald. Omdat je dit wil vergelijken met de boetebedragen die betaald werden voor leden die niet in het bestuur zitten, wil je deze boetebedragen ook opnemen in de tweede kolom van je resultaat. Sorteer je antwoord eerst op functie en daarna op het boetebedrag.

OPLOSSING
SELECT bs.functie, b.bedrag
FROM boetes AS b
FULL OUTER JOIN bestuursleden AS bs ON bs.spelersnr = b.spelersnr
WHERE bs.eind_datum IS NULL
ORDER BY functie, bedrag
OPGAVE
Geef voor elke mannelijke speler wiens naam minstens 2 keer de letter 'e' bevat zijn functie die hij op dit moment uitoefent, als die er op dit moment één heeft. Sorteer op naam en functie.

OPLOSSING
SELECT s.naam, s.geslacht, bs.functie
FROM spelers AS s
LEFT OUTER JOIN bestuursleden AS bs ON bs.spelersnr = s.spelersnr AND bs.eind_datum IS NULL
WHERE s.naam ilike '%e%e%' AND s.geslacht = 'M'
OPGAVE
Geef alle spelers die geen boete gekregen hebben en niet in Den Haag wonen. Sorteer op jaar van toetreden.

OPLOSSING
SELECT s.spelersnr, s.naam, s.plaats
FROM spelers AS s
LEFT OUTER JOIN boetes AS b USING (spelersnr)
WHERE b.spelersnr IS NULL AND s.plaats != 'Den Haag'
ORDER BY s.jaartoe
OPGAVE
Geef een aflopend gesorteerde lijst van de nummers van alle spelers waarvoor nog geen boete werd betaald en die nog nooit in het bestuur van de tennisvereniging hebben gezeten.

OPLOSSING
SELECT s.spelersnr
FROM spelers AS s
LEFT OUTER JOIN boetes AS b ON s.spelersnr = b.spelersnr
LEFT OUTER JOIN bestuursleden AS bs ON s.spelersnr = bs.spelersnr
WHERE b.spelersnr IS NULL AND bs.spelersnr IS NULL
ORDER BY s.spelersnr DESC
OPGAVE
Geef van elke speler (die wedstrijden gespeeld heeft en boetes gekregen heeft) wonend in Rijswijk het spelersnr, de naam, de lijst met boetebedragen en de lijst met teams waarvoor hij/zij een wedstrijd gespeeld heeft. Geef het resultaat volgens oplopend spelersnr en boetebedrag.

OPLOSSING
SELECT s.spelersnr, s.naam, b.bedrag, w.teamnr
FROM spelers AS s
INNER JOIN boetes AS b
on b.spelersnr = s.spelersnr
INNER JOIN wedstrijden AS w
on w.spelersnr = s.spelersnr
WHERE s.plaats = 'Rijswijk'
ORDER BY spelersnr
OPGAVE
Geef het spelersnummer en bondsnummer van alle spelers die jonger zijn dan de speler met bondsnummer 8467. gebruik een INNER JOIN.

OPLOSSING
SELECT s.spelersnr, s.bondsnr
FROM spelers AS s1
INNER JOIN spelers AS s ON s.geb_datum > s1.geb_datum
WHERE s1.bondsnr = '8467'
OPGAVE
Geef voor alle vrouwelijke spelers die in Den Haag, Zoetermeer of Leiden wonen het spelersnummer, hun woonplaats en een lijst van de teams waarvoor ze ooit gespeeld hebben, als ze ooit een wedstrijd gespeeld hebben.

OPLOSSING
SELECT s.spelersnr, s.plaats, w.teamnr
FROM spelers AS s
LEFT OUTER JOIN wedstrijden AS w USING (spelersnr)
WHERE s.geslacht = 'V' AND s.plaats IN ('Den Haag', 'Zoetermeer', 'Leiden')
OPGAVE
Geef voor de actieve bestuursleden zonder boete hun laatste gespeelde wedstrijd (die met het hoogste wedstrijdnummer). Sorteer aflopend op spelersnr.

OPLOSSING
SELECT bs.spelersnr, MAX(w.wedstrijdnr) AS laatstewedstrijd
FROM bestuursleden AS bs
INNER JOIN wedstrijden AS w ON bs.spelersnr = w.spelersnr
LEFT OUTER JOIN boetes AS b ON b.spelersnr = bs.spelersnr
WHERE bs.eind_datum IS NULL AND b.spelersnr IS NULL
GROUP BY bs.spelersnr
ORDER BY bs.spelersnr DESC

SELECT bs.spelersnr, MAX((SELECT wedstrijden.wedstrijdnr FROM wedstrijden WHERE wedstrijden.spelersnr = wbs.spelersnr)) AS laatstewedstrijd
FROM bestuursleden AS bs
LEFT OUTER JOIN boetes AS b ON b.spelersnr = bs.spelersnr
WHERE bs.eind_datum IS NULL AND b.spelersnr IS NULL
ORDER BY bs.spelersnr DESC```


## OPGAVE ##

Geef per team de leeftijd van de aanvoerder (tip: postgresql heeft een AGE() functie) en het aantal verschillende spelers dat voor dit team gespeeld heeft.
Alleen teams waarvoor wedstrijden zijn gespeeld en die een aanvoerder hebben, moeten vermeld worden.
Sorteer op leeftijd en daarna op aantal verschillende spelers en daarna op teamnr.

### OPLOSSING ###

```sql
SELECT t.teamnr, extract (year FROM age(s.geb_datum))||' jaar' AS leeftijd, COUNT(distinct w.spelersnr) AS aantalspelers
FROM teams AS t
INNER JOIN spelers AS s ON s.spelersnr = t.spelersnr
INNER JOIN wedstrijden AS w ON w.teamnr = t.teamnr
GROUP BY t.teamnr, s.geb_datum
ORDER BY s.geb_datum DESC, aantalspelers, t.teamnr
OPGAVE
Geef per team het hoogste wedstrijdnummer van een wedstrijd, gespeeld door een bestuurslid (actief en niet meer actief) die geen boete heeft gekregen. Sorteer op teamnr.

OPLOSSING
SELECT t.teamnr, MAX(w1.wedstrijdnr) AS laatstewedstrijd
FROM bestuursleden AS bs --alle bestuursleden, actief en niet actief
INNER JOIN wedstrijden AS w1 ON w1.spelersnr = bs.spelersnr --wedstrijden gespeeld door bestuursleden
LEFT OUTER JOIN boetes AS b ON b.spelersnr = bs.spelersnr
WHERE b.spelersnr IS NULL
GROUP BY t.teamnr
ORDER BY t.teamnr
OPGAVE
Welke reizigers hebben al meer dan 1 reis ondernomen waarvoor ze meer dan 2,5 miljoen euro moesten betalen? Sorteer op naam

OPLOSSING
SELECT k.naam, COUNT(d.reisnr) AS aantal_reizen
FROM klanten AS k
INNER JOIN deelnames AS d USING (klantnr)
INNER JOIN reizen AS r USING (reisnr)
WHERE prijs > 2.5
GROUP BY k.klantnr, k.naam
HAVING COUNT(d.reisnr) > 1
ORDER BY k.naam
OPGAVE
Op welke planeten verblijft men gemiddeld langer dan 2 dagen? Sorteer op objectnaam.

OPLOSSING
SELECT h.objectnaam, AVG(b.verblijfsduur)
FROM bezoeken AS b
INNER JOIN hemelobjecten AS h USING (objectnaam)
WHERE h.satellietvan = 'Zon'
GROUP BY h.objectnaam
HAVING AVG(b.verblijfsduur) > 2
ORDER BY h.objectnaam
OPGAVE
Maak een lijst met een overzicht van de reizen en het aantal deelnemers van elke reis. Orden de lijst dalend op basis van het aantal deelnemers, als er eenzelfde aantal deelnemers is, moeten deze stijgend geordend worden volgens reisnummer.

OPLOSSING
SELECT reisnr, COUNT(klantnr) AS deelnemers
FROM deelnames
GROUP BY reisnr
ORDER BY deelnemers DESC, reisnr;
OPGAVE
Maak een lijst met een overzicht van de reizen en het aantal deelnemers van elke reis. Orden de lijst dalend op basis van het aantal deelnemers, als er eenzelfde aantal deelnemers is, moeten deze stijgend geordend worden volgens reisnummer. Zorg dat reizen zonder deelnames ook in het resultaat staan.

OPLOSSING
SELECT r.reisnr, COUNT(d.klantnr) AS deelnemers
FROM reizen AS r
LEFT OUTER JOIN deelnames AS d USING (reisnr)
GROUP BY r.reisnr
ORDER BY deelnemers DESC, r.reisnr;
OPGAVE
Welke klanten (klantnaam, geboortedatum) zijn op een ruimtereis vertrokken in het jaar dat ze 45 jaar geworden zijn? Het resultaat moet stijgend gesorteerd worden op de geboortedatum.

OPLOSSING
SELECT k.naam AS klantnaam, k.geboortedatum
FROM deelnames d
INNER JOIN klanten k USING (klantnr)
INNER JOIN reizen r USING (reisnr)
WHERE EXTRACT(year FROM r.vertrekdatum) - EXTRACT(year FROM k.geboortedatum) = 45
GROUP BY k.naam, k.geboortedatum
ORDER BY geboortedatum
OPGAVE
Welke planeten met meer dan 7 manen worden bezocht (met of zonder verblijf)? Sorteer aflopend op basis van het aantal manen. Let erop dat je planeten die meerdere keren bezocht worden, niet dubbel telt.

OPLOSSING
SELECT p.objectnaam
FROM hemelobjecten AS p
INNER JOIN hemelobjecten AS m ON m.satellietvan = p.objectnaam
INNER JOIN bezoeken AS b ON b.objectnaam = p.objectnaam
INNER JOIN hemelobjecten AS zon ON zon.objectnaam = p.satellietvan AND zon.satellietvan IS NULL
GROUP BY p.objectnaam
HAVING COUNT(distinct m.objectnaam) > 7
ORDER BY COUNT(distinct m.objectnaam) DESC
OPGAVE
Geef een lijst met alle planeten en per planeet zijn satellieten. Sorteer op planeet en daarna op satelliet.

OPLOSSING
SELECT p.objectnaam AS planeet, m.objectnaam AS maan
FROM hemelobjecten AS p
LEFT OUTER JOIN hemelobjecten AS m ON m.satellietvan = p.objectnaam
WHERE p.satellietvan = 'Zon'
ORDER BY planeet, maan
OPGAVE
Geef de leeftijd van de jongste klant op moment van vertrek.

OPLOSSING
SELECT EXTRACT(year FROM min(age(r.vertrekdatum, k.geboortedatum))) AS jongsteleeftijd
FROM deelnames AS d
INNER JOIN reizen AS r USING (reisnr)
INNER JOIN klanten AS k USING (klantnr)
OPGAVE
Geef de diameter van de grootste, niet bezochte maan (satelliet van een planeet).

OPLOSSING
SELECT MAX(m.diameter) AS grootstemaan
FROM hemelobjecten p
INNER JOIN hemelobjecten m ON m.satellietvan = p.objectnaam
LEFT OUTER JOIN bezoeken AS b ON b.objectnaam = m.objectnaam
WHERE p.satellietvan = 'Zon' AND b.objectnaam IS NULL
OPGAVE
Geef voor elk hemelobject de minimale en maximale gemiddelde afstand tot zijn zon (de centrale ster in een sterrenstELSEl) als je weet dat de kolom 'afstand' de gemiddelde afstand bevat tot het hemelobject waarrond ze draaien. Met de grootte van het hemelobject hoef je geen rekening te houden. Sorteer op minimale afstand en op objectnaam.

OPLOSSING
SELECT satelliet.objectnaam,
CASE
    WHEN satelliet.afstand IS NULL then 0
    WHEN planeet.afstand IS NULL then satelliet.afstand
    ELSE planeet.afstand + satelliet.afstand
end AS maximale_afstand,
CASE
    WHEN satelliet.afstand IS NULL then 0
    WHEN planeet.afstand IS NULL then satelliet.afstand
    ELSE planeet.afstand - satelliet.afstand
end  AS minimale_afstand
FROM hemelobjecten satelliet
LEFT OUTER JOIN hemelobjecten planeet ON planeet.objectnaam = satelliet.satellietvan
ORDER BY minimale_afstand, satelliet.objectnaam

SELECT satelliet.objectnaam,
COALESCE(planeet.afstand + satelliet.afstand, satelliet.afstand, 0) AS maximale_afstand,
COALESCE(planeet.afstand - satelliet.afstand, satelliet.afstand, 0) AS minimale_afstand
FROM hemelobjecten AS satellite
LEFT OUTER JOIN hemelobjecten AS planeet ON planeet.objectnaam = satelliet.satellietvan
ORDER BY minimale_afstand, satelliet.objectnaam
OPGAVE
Geef voor elke speler die een wedstrijd heeft gespeeld het spelersnr en het totaal aantal boetes. Spelers die een wedstrijd gespeeld hebben, maar geen boetes hebben, moeten ook getoond worden. Sorteer op het aantal boetes en op spelersnr;

OPLOSSING
SELECT w.spelersnr, aantalboetes
FROM wedstrijden AS w
LEFT OUTER JOIN (SELECT spelersnr, COUNT(*) AS aantalboetes FROM boetes GROUP BY spelersnr) AS aboetes ON aboetes.spelersnr = w.spelersnr
GROUP BY w.spelersnr, aantalboetes
ORDER BY aantalboetes, w.spelersnr
OPGAVE
Geef voor alle spelers die geen penningmeester zijn of zijn geweest alle gewonnen wedstrijden, gesorteerd op wedstrijdnummer.

OPLOSSING
SELECT w.spelersnr, w.wedstrijdnr
FROM wedstrijden AS w
WHERE w.gewonnen > w.verloren AND w.spelersnr NOT IN (SELECT spelersnr FROM bestuursleden WHERE functie='Penningmeester')
ORDER BY w.wedstrijdnr
OPGAVE
Geef van elke speler het spelersnr, de naam en het verschil tussen zijn of haar jaar van toetreding en het gemiddeld jaar van toetreding van de spelers die in dezelfde plaats wonen. Sorteer op spelersnr. Toon 3 getallen na de komma, zet het verschil om naar het numeric type met precisie van 5 en een schaal van 3.

OPLOSSING
SELECT s.spelersnr, s.naam, s.voorletters, round(jaartoe - (SELECT AVG(jaartoe) FROM spelers WHERE plaats = s.plaats), 3) AS numeric
FROM spelers AS s
ORDER BY 1;
OPGAVE
Je kan per speler berekenen hoeveel boetes die speler heeft gehad en wat het totaalbedrag per speler is. Pas nu deze querie aan zodat per verschillend aantal boetes wordt getoond hoe vaak dit aantal boetes voorkwam.Sorteer eerst op de eerste kolom en daarna op de tweede kolom.

OPLOSSING
SELECT b1.aantalboetes AS a, COUNT(spelersnr)
FROM (SELECT spelersnr, COUNT(*) AS aantalboetes FROM boetes GROUP BY spelersnr) AS b1
GROUP BY b1.aantalboetes
ORDER BY 1, 2
OPGAVE
Geef van alle spelers het verschil tussen het jaar van toetreding en het geboortejaar, maar geef alleen die spelers waarvan dat verschil groter is dan 20. Sorteer deze gegevens beginnend bij de eerste kolom, eindigend bij de laatste kolom.

OPLOSSING
SELECT s.spelersnr, s.naam, s.voorletters, (jaartoe - EXTRACT(year FROM geb_datum)) AS toetredingsleeftijd
FROM spelers AS s
WHERE jaartoe - EXTRACT(year FROM geb_datum) > 20
ORDER BY 1, 2, 3, 4
OPGAVE
Geef van elke speler het spelersnr, de naam en het verschil tussen zijn of haar jaar van toetreding en het gemiddeld jaar van toetreding van de spelers die in dezelfde plaats wonen. Sorteer op spelersnr. Zet het berekende verschil om naar het datatype numeric met precisie 5 en schaal 3.

OPLOSSING
SELECT s.spelersnr, s.naam, s.voorletters, round(jaartoe - (SELECT AVG(jaartoe) FROM spelers WHERE plaats = s.plaats), 3) AS numeric
FROM spelers AS s
ORDER BY 1
OPGAVE
Geef een lijst van alle spelers (spelersnr en woonplaats) die met minstens twee in dezelfde plaats wonen. Sorteer aflopend op woonplaats.

OPLOSSING
SELECT s.spelersnr, s.plaats
FROM spelers AS s
WHERE EXISTS (SELECT spelersnr FROM spelers WHERE plaats = s.plaats AND spelersnr <> s.spelersnr)
ORDER BY s.plaats DESC
OPGAVE
Geef de spelersgegevens van de speler(s) met het hoogste bedrag (voor één boete, niet het totaalbedrag). Als twee spelers een even hoge boete gehad hebben, moeten beide spelers getoond worden (LIMIT is dus geen optie). Sorteer alfabetisch op naam en voorletters.

OPLOSSING
SELECT s.spelersnr, s.voorletters, s.naam
FROM boetes AS b
INNER JOIN spelers AS s USING(spelersnr)
WHERE b.bedrag = (SELECT MAX(bedrag) FROM boetes)
ORDER BY 3,2
OPGAVE
Geef een lijst van alle hemelobjecten die meer keer bezocht gaan worden dan Jupiter. Sorteer op objectnaam.

OPLOSSING
SELECT h.objectnaam, h.diameter
FROM hemelobjecten AS h
INNER JOIN bezoeken AS b ON b.objectnaam = h.objectnaam
GROUP BY h.objectnaam, h.diameter
HAVING COUNT(b.*) >
    (SELECT COUNT(*)
    FROM bezoeken
    INNER JOIN hemelobjecten ON bezoeken.objectnaam = hemelobjecten.objectnaam
    WHERE hemelobjecten.objectnaam = 'Jupiter')
ORDER BY 1
OPGAVE
Geef het hemellichaam dat het laatst bezocht is. Gebruik hiervoor de laatste vertrekdatum van de reis en laatste volgnummer van bezoek. Tip: gebruik hiervoor een rij-subquery. Gebruik geen limit of top.

OPLOSSING
SELECT objectnaam FROM bezoeken
WHERE
(reisnr, volgnr) = (
    SELECT reisnr, MAX(volgnr)
    FROM bezoeken
    WHERE reisnr = (
        SELECT reisnr
        FROM reizen
        WHERE vertrekdatum = (
            SELECT MAX(vertrekdatum) AS vertrekdatum
            FROM reizen
            )
        )
    GROUP BY reisnr
    )
OPGAVE
Geef het reisnr, de prijs en vertrekdatum van de reis met de hoogste gemiddelde verblijfsduur op een planeet (=som van de verblijfsduur / aantal bezoeken per reis).

OPLOSSING
SELECT r.reisnr, r.prijs, r.vertrekdatum
FROM    (
    SELECT reisnr, SUM(verblijfsduur)/COUNT(volgnr) AS gem_duur
    FROM bezoeken
    GROUP BY reisnr
    ) AS gemiddelde_duur
INNER JOIN reizen AS r ON r.reisnr = gemiddelde_duur.reisnr
WHERE gem_duur = (
    SELECT MAX(gem_duur)
    FROM (
        SELECT reisnr, SUM(verblijfsduur)/COUNT(volgnr) AS gem_duur
        FROM bezoeken
        GROUP BY reisnr
        ) AS gemiddelde_duur
    )
OPGAVE
Geef de planeet (draait dus rond de zon) met de meeste satellieten. Sorteer op objectnaam.

OPLOSSING
SELECT objectnaam
FROM (
    SELECT h.satellietvan AS objectnaam, COUNT(h.objectnaam) AS aantal_manen
    FROM hemelobjecten AS h
    WHERE h.satellietvan <> 'Zon'
    GROUP BY h.satellietvan
        ) AS aantal_manen
WHERE aantal_manen = (
    SELECT MAX(aantal_manen)
    FROM    (
        SELECT h.satellietvan AS objectnaam, COUNT(h.objectnaam) AS aantal_manen
        FROM hemelobjecten AS h
        WHERE h.satellietvan <> 'Zon'
        GROUP BY h.satellietvan
        ) AS aantal_manen
    )
ORDER BY objectnaam
OPGAVE
Geef het op één na kleinste hemellichaam. Je kan dit vinden door handig gebruik te maken van expliciete joins en een doorsnedevoorwaarde. Tip: probeer eerst een lijst te krijgen van alle hemelobjecten en het aantal hemellichaam dat kleiner is dan dat hemelobject.

OPLOSSING
SELECT objectnaam, aantalkleiner
FROM    (
    SELECT hg.objectnaam, COUNT(hk.objectnaam) AS aantalkleiner
    FROM hemelobjecten AS hg
    INNER JOIN hemelobjecten AS hk ON hg.diameter > hk.diameter
    GROUP BY hg.objectnaam
    ) AS h
WHERE aantalkleiner = 1