Experience is a great advantage. The problem is that when you get the experience, you're too damned old to do anything about it.
—Jimmy Connors

Oefeningen SQL op databank voor een tennisvereniging

We maken de stap naar een groter schema, met 5 gekoppelde tabellen. Dit schema is de basis van het boek ‘Het SQL Leerboek’, 7de druk, Rick van der Lans, Academic Service. Dit handboek werd tot voor kort als basishandboek gebruikt in de opleiding TI.

De tennisdatabank

Relationeel fysiek model

De onderstaande figuur toont het ERD voor dit schema. Bestudeer dit schema grondig en lees aandachtig de uitleg in de volgende secties. Op dit schema volgen veel oefeningen.

Belangrijke informatie over de vereniging / databank

De tennisvereniging is opgericht in 1970 en vanaf het begin wordt een aantal administratieve gegevens in een database opgeslagen. Deze database bestaat uit de volgende tabellen: spelers, teams, wedstrijden, boetes en bestuursleden.

De spelerstabel bevat gegevens over spelers die lid zijn van de tennisvereniging, zoals namen, adressen en geboortedatums. Toetreding tot de vereniging vindt altijd plaats op 1 januari van een bepaald jaar. Spelers kunnen dus niet midden in een jaar lid worden. De spelerstabel bevat geen historische gegevens. Als een speler zijn of haar lidmaatschap opzegt, verdwijnt hij of zij uit de tabel. Ook bij eventuele verhuizingen wordt het oude adres overschreven met het nieuwe adres, het oude adres wordt dus nergens bewaard.

De tennisvereniging kent twee soorten leden: recreatiespelers en wedstrijdspelers. De eerste groep speelt alleen onderlinge wedstrijden, dus geen wedstrijden tegen spelers van andere verenigingen. De resultaten van de onderlinge wedstrijden worden niet geregistreerd. Wedstrijdspelers spelen in teamverband tegen spelers van andere verenigingen. De resultaten van deze wedstrijden worden wel bijgehouden. Elke speler heeft een uniek nummer, ongeacht of deze wedstrijdspeler is of niet. Dit spelersnummer wordt door de vereniging uitgedeeld. Het is verplicht dat elke wedstrijdspeler bij de tennisbond geregistreerd staat.

De bond, die een nationaal instituut is, geeft elke wedstrijdspeler een uniek bondsnummer. Dit bondsnummer bestaat meestal uit cijfers, maar kan ook letters bevatten. Als een wedstrijdspeler geen wedstrijden meer speelt, maar recreatiespeler wordt, vervalt het bondsnummer. Let wel, recreatiespelers hebben dus geen bondsnummer, maar wel een spelersnummer.

De tennisvereniging heeft een aantal teams dat meedoet in competities. Voor elk team worden de aanvoerder en de divisie waarin het team op dat moment uitkomt, geregistreerd. De aanvoerder hoeft geen wedstrijden voor het team gespeeld te hebben. Het zou kunnen dat een bepaalde speler op een bepaald moment aanvoerder van twee of meer teams is. Ook in deze tabel wordt geen historie bijgehouden. Bij promotie of degradatie van een team naar een andere divisie wordt de geregistreerde divisie eenvoudigweg overschreven. Hetzelfde geldt voor de aanvoerder van een team: bij wisseling wordt het nummer van de oude aanvoerder overschreven.

Een team bestaat uit een aantal spelers. Als een team tegen een team van een andere vereniging speelt, speelt elke speler van dat team een wedstrijd tegen een speler van het andere team (we gaan voor het gemak ervan uit dat wedstrijden waarbij koppels tegen elkaar spelen niet voorkomen). Het team waarvan de meeste spelers hun wedstrijd winnen is winnaar.

Een team bestaat niet altijd uit dezelfde groep spelers. Bij ziekte of vakanties zijn soms invallers nodig. Een speler kan dus voor meerdere teams uitkomen. Als we spreken over ‘de spelers van een team’, dan bedoelen we dus de spelers die minstens één wedstrijd voor het team gespeeld hebben. Nogmaals, alleen spelers met een bondsnummer mogen officiële wedstrijden spelen.

Een tenniswedstrijd is opgebouwd uit een aantal sets. Degene die de meeste sets heeft gewonnen, is winnaar. Voor elke wedstrijd wordt vooraf bepaald bij hoeveel gewonnen sets de wedstrijd gewonnen is. Over het algemeen wordt de wedstrijd gestopt als een van de twee spelers twee of drie sets gewonnen heeft. Mogelijke eindstanden van een tenniswedstrijd zijn dus 2-1 of 2-0 als gespeeld wordt totdat een van de spelers twee sets gewonnen heeft (best of three), of 3-2, 3-1 of 3-0 als gespeeld wordt tot drie gewonnen sets (best of five). Een speler kan zijn of haar wedstrijd winnen of verliezen, gelijkspel is niet mogelijk. In de wedstrijdentabel wordt voor elke wedstrijd apart bijgehouden welke speler de wedstrijd heeft gespeeld en voor welk team. Tevens wordt geregistreerd hoeveel sets de speler heeft gewonnen en verloren. Hieruit is af te leiden of hij of zij de wedstrijd gewonnen heeft.

Voor onreglementair gedrag van spelers (te late opkomst, agressief gedrag of niet verschijnen) worden door de bond boetes opgelegd. Boetes worden door de vereniging betaald. Na betaling worden ze in de boetestabel geregistreerd. Zolang een speler wedstrijden speelt, blijven alle boetes bewaard die voor hem of haar opgelegd en betaald zijn.

Als een speler de vereniging verlaat, worden al zijn of haar gegevens in de vijf tabellen vernietigd. Als de vereniging een team terugtrekt, worden alle gegevens over dat team uit de teams- en wedstrijdentabel verwijderd. Als een wedstrijdspeler stopt met het spelen van wedstrijden en hij of zij dus weer recreant wordt, worden alle wedstrijd- en boetegegevens uit de desbetreffende tabellen verwijderd.

Sinds 1 januari 1990 wordt in de bestuursledentabel bijgehouden wie er in het bestuur zit. Vier functies worden onderscheiden: voorzitter, penningmeester, secretaris en algemeen lid. Elk jaar op 1 januari wordt een nieuw bestuur gekozen. Wanneer een speler een bestuursfunctie bekleedt, worden de begin- en einddatum hiervan geregistreerd. Als iemand nog actief is, wordt er geen einddatum ingevuld.

CREATE statements

Je hoeft dit schema niet zelf te bouwen. Het is bereikbaar via de pooling-connectie, databank ‘oefeningen’, schema ‘tennis’ of ook via connectie 54321 in de tabel ‘basis_rdbms’. Dit schema zit al in het search_path. Daarom werkt volgende code zonder eerst nog het schema te vermelden:

SELECT *
FROM spelers -- dus niet nodig om tennis.spelers te schrijven

Hieronder geven we de SQL code om de vijf tabellen aan te maken, maar dat hoef je dus niet te doen, want dit schema staat gewoon klaar. We verwachten wel dat je alle regels in deze code begrijpt. De CHECK beperkingen (CONSTRAINTS) zijn nieuw, maar vermoedelijk zijn die niet zo moeilijk om te ontcijferen. Het is echt belangrijk om het schema goed te bestuderen.

CREATE  TABLE tennis.spelers (
  spelersnr            integer  NOT NULL ,
  naam                 char(15)  NOT NULL ,
  voorletters          char(3)  NOT NULL ,
  geb_datum            date   ,
  geslacht             char(1)  NOT NULL ,
  jaartoe              smallint  NOT NULL ,
  straat               varchar(30)  NOT NULL ,
  huisnr               char(4)   ,
  postcode             char(6)   ,
  plaats               varchar(30)  NOT NULL ,
  telefoon             char(13)   ,
  bondsnr              char(4)   ,
  CONSTRAINT spelers_pkey PRIMARY KEY ( spelersnr ) ,
  CONSTRAINT spelers_postcode_check CHECK ( (postcode LIKE '______' ) ) ,
  CONSTRAINT spelers_jaartoe_check CHECK ( (jaartoe > 1969) ) ,
  CONSTRAINT spelers_geslacht_check CHECK ( (geslacht IN ('M', 'V')) )
);

CREATE  TABLE tennis.teams (
  teamnr               integer  NOT NULL ,
  spelersnr            integer  NOT NULL ,
  divisie              char(6)  NOT NULL ,
  CONSTRAINT teams_pkey PRIMARY KEY ( teamnr ) ,
  CONSTRAINT teams_spelersnr_fkey FOREIGN KEY ( spelersnr ) REFERENCES tennis.spelers( spelersnr ) ,
  CONSTRAINT teams_divisie_check CHECK ( (divisie IN ('ere', 'tweede')) )
);

CREATE  TABLE tennis.wedstrijden (
  wedstrijdnr          integer  NOT NULL ,
  teamnr               integer  NOT NULL ,
  spelersnr            integer  NOT NULL ,
  gewonnen             smallint  NOT NULL ,
  verloren             smallint  NOT NULL ,
  CONSTRAINT wedstrijden_pkey PRIMARY KEY ( wedstrijdnr ),
  CONSTRAINT wedstrijden_spelersnr_fkey FOREIGN KEY ( spelersnr ) REFERENCES
       tennis.spelers( spelersnr )   ,
  CONSTRAINT wedstrijden_teamnr_fkey FOREIGN KEY ( teamnr ) REFERENCES tennis.teams( teamnr )  ,
  CONSTRAINT wedstrijden_verloren_check CHECK ( ((verloren >= 0) AND (verloren <= 3)) ) ,
  CONSTRAINT wedstrijden_gewonnen_check CHECK ( ((gewonnen >= 0) AND (gewonnen <= 3)) )
);

CREATE  TABLE tennis.bestuursleden (
  spelersnr            integer  NOT NULL ,
  begin_datum          date  NOT NULL ,
  eind_datum           date   ,
  functie              char(20)   ,
  CONSTRAINT bestuursleden_pkey PRIMARY KEY ( spelersnr, begin_datum ),
  CONSTRAINT bestuursleden_spelersnr_fkey FOREIGN KEY ( spelersnr ) REFERENCES
      tennis.spelers( spelersnr ) ,
  CONSTRAINT bestuursleden_check CHECK ( (begin_datum < eind_datum) ) ,
  CONSTRAINT bestuursleden_begin_datum_check CHECK ( (begin_datum >= '1990-01-01'::date) )
);

CREATE  TABLE tennis.boetes (
  betalingsnr          integer  NOT NULL ,
  spelersnr            integer  NOT NULL ,
  datum                date  NOT NULL ,
  bedrag               numeric(7,2)  NOT NULL ,
  CONSTRAINT boetes_pkey PRIMARY KEY ( betalingsnr ),
  CONSTRAINT boetes_spelersnr_fkey FOREIGN KEY ( spelersnr ) REFERENCES	tennis.spelers( spelersnr ) ,
  CONSTRAINT boetes_datum_check CHECK ( (datum >= '1969-12-31'::date) ) ,
  CONSTRAINT boetes_bedrag_check CHECK ( (bedrag > (0)::numeric) )
);

Enkele aandachtspunten

In het ERD (en de CREATE code willen we even wijzen op enkele punten:

• Merk op dat het spelersnummer een centrale rol in het schema heeft. Het is in één tabel de primaire sleutel en in de vier andere tabellen de externe sleutel (‘foreign key’).
• De bestuursledentabel heeft een samengestelde primaire sleutel. In de figuur zie je dat aan de twee sleutelicoontjes, in de code staan er twee kolommen in de PRIMARY KEY.
• Een veelgemaakte fout is geen rekening houden met het feit dat het spelersnummer in de teamstabel het nummer is van de kapitein van een team. Om te weten wie effectief wedstrijden gespeeld heeft, moet je in de wedstrijdentabel het spelersnummer gebruiken.

Eenvoudige oefeningen

We beginnen met enkele eenvoudige oefeningen, vooral bedoeld om dit schema wat beter te leren kennen. Zoals altijd: maak ze zelf en bekijk dan pas de oplossing.

Toon alle spelers uit Zoetermeer die lid werden van de club vóór 1984). Je moet de kolominhouden en -hoofdingen van de figuur bekomen.

Toon / verberg oplossing

SELECT spelersnr, naam || ' ' || voorletters AS naam, jaartoe
FROM spelers
WHERE plaats = 'Zoetermeer' AND jaartoe < 1984

Maak een lijst van alle teams waarvoor lidnr 27 de kapitein is.

Toon / verberg oplossing

SELECT *
FROM teams
WHERE spelersnr = 27

Geef een overzicht van alle gewonnen tenniswedstrijden.

Toon / verberg oplossing

Je wint een wedstrijd als je meer sets wint dan verliest:

SELECT *
FROM wedstrijden
WHERE gewonnen > verloren

Geef een overzicht van alle wedstrijden die speler 112 gespeeld heeft. Bereken voor elke van deze wedstrijden met hoeveel sets deze speler gewonnen of verloren heeft.

Toon / verberg oplossing

De figuur toont dat je een nieuwe kolom moet toevoegen die het resultaat is van een eenvoudige berekening:

SELECT wedstrijdnr, spelersnr, abs(gewonnen - verloren) AS verschil
FROM wedstrijden
WHERE spelersnr = 112

Maak een lijst van alle betaalde boetes.

Toon / verberg oplossing

SELECT *
FROM boetes

Voeg in de lijst van vorige oefening ook de naam van de speler toe (naam en eerste letter(s) in één kolom). De screenshot van de figuur toont niet alle rijen.

Toon / verberg oplossing

De informatie die je nodig hebt staat nu verspreid over twee tabellen, dus je hebt een JOIN nodig

SELECT b.betalingsnr, b.spelersnr, s.naam || ' ' || s.voorletters AS naam, b.datum, b.bedrag
FROM boetes b INNER JOIN spelers s ON b.spelersnr = s.spelersnr

Geef het kleinste en grootste boetebedrag.

Toon / verberg oplossing

SELECT min(bedrag) AS min, max(bedrag) AS max
FROM boetes

Geef een overzicht van alle bestuursleden die nu in functie zijn. Toon hun functie. Geef ook hun namen (naam en voorletters in één kolom) zoals in de figuur.

Toon / verberg oplossing

SELECT b.spelersnr, s.naam || ' ' || s.voorletters AS naam, b.begin_datum, b.functie
FROM bestuursleden b INNER JOIN spelers s ON b.spelersnr = s.spelersnr
WHERE eind_datum IS null

Maak een lijst van alle vrouwelijke spelers die niet in Leiden wonen.

Toon / verberg oplossing

SELECT spelersnr, naam, plaats, geslacht
FROM spelers
WHERE geslacht = 'V' AND plaats != 'Leiden'

Boetes

Wat is het gemiddelde boetebedrag? Hoeveel boetes werden al betaald?

Toon / verberg oplossing

SELECT round(avg(bedrag)) AS average, count(bedrag) AS "number of fines"
FROM boetes

Geef een overzicht van alle boetes groter dan € 30. Toon het bedrag in eurocent. Geef ook het spelersnummer en de naam van de speler die de boete kreeg.

Toon / verberg oplossing

SELECT b.spelersnr, s.naam, round(bedrag*100) AS "bedrag in centen"
FROM boetes b INNER JOIN spelers s ON b.spelersnr = s.spelersnr AND bedrag > 30

Vertrek van de vorige oefening: een lijst van alle spelers die een boete van meer dan € 30 kregen. Het verschil is nu dat we enkel een lijst van spelers willen en geen lijst van boetes. Een speler die meer dan één boete kreeg (e.g. Cools heeft een boete van zowel € 75 als € 100) mag maar één keer voorkomen in deze lijst.

Toon / verberg oplossing

SELECT DISTINCT spelersnr
FROM boetes
WHERE bedrag > 30

Wedstrijden, spelers, kapiteins, …

Maak een lijst van alle gewonnen wedstrijden gespeeld door leden van team 2. Toon het spelersnr van de winnende speler en ook het nummer van de kapitein van het team.

Toon / verberg oplossing

SELECT w.wedstrijdnr, w.spelersnr, w.teamnr, t.spelersnr AS captain
FROM wedstrijden w INNER JOIN teams t ON w.teamnr = t.teamnr
WHERE w.teamnr = 2 AND gewonnen-verloren > 0

Genereer een lijst van alle competitiespelers. Niet alle spelers van onze club spelen in een competitie, maar zij die wel in officiële wedstrijden spelen moeten lid zijn van de nationale bond (de figuur toont niet het volledige overzicht).

Toon / verberg oplossing

SELECT spelersnr, naam
FROM spelers
WHERE bondsnr IS NOT null

Toon het overzicht van vorige oefening, maar alleen voor de vrouwelijke spelers.

Toon / verberg oplossing

SELECT spelersnr, naam, geslacht
FROM spelers
WHERE bondsnr IS NOT null AND geslacht = 'V'

Toon naam en voorletters, team en divisie voor de kapitein van elk team.

Toon / verberg oplossing

SELECT t.teamnr,t.spelersnr, s.naam || ' ' || s.voorletters AS kapitein, t.divisie
FROM teams t INNER JOIN spelers s ON t.spelersnr = s.spelersnr

Beperk de vorige lijst tot de vrouwelijke kapiteins.

Toon / verberg oplossing

SELECT t.teamnr,t.spelersnr, s.naam || ' ' || s.voorletters AS kapitein, t.divisie
FROM teams t INNER JOIN spelers s ON t.spelersnr = s.spelersnr
WHERE s.geslacht = 'V'

Moeilijkere oefeningen

Geef een lijst met het spelersnummer, de naam van de speler, de datum van de boete en het bedrag van de boete van al de spelers die een boete gekregen hebben met een bedrag groter dan € 45,50 en in Rijswijk wonen. Sorteer op spelersnr en het volgnummer van de boete.

Toon / verberg oplossing

SELECT spelers.spelersnr, spelers.naam, boetes.datum, boetes.bedrag
FROM spelers INNER JOIN boetes ON spelers.spelersnr = boetes.spelersnr
WHERE boetes.bedrag > 45.50 AND spelers.plaats = 'Rijswijk'
ORDER BY spelers.spelersnr, boetes.betalingsnr;

Geef voor elke wedstrijd het wedstrijdnummer en de volledige naam van de aanvoerder van het team waarvoor de wedstrijd werd gespeeld. Sorteer je resultaat volgens het wedstrijdnummer in oplopende volgorde. TIP: je zal hier een JOIN moeten doen met meer dan twee tabellen.

Toon / verberg oplossing

SELECT W.wedstrijdnr, T.spelersnr, naam, voorletters
FROM wedstrijden W INNER JOIN teams T on W.teamnr = T.teamnr
  INNER JOIN spelers S on T.spelersnr = S.spelersnr
ORDER BY 1

Maak een overzicht waarbij je per woonplaats die minstens één letter ‘o’ (hoofdletter of kleine letter allebei OK) aangeeft hoeveel spelers er wonen in die gemeente. Sorteer op plaats.

Toon / verberg oplossing

SELECT plaats, COUNT(*) AS aantal
FROM spelers
GROUP BY plaats
HAVING LOWER(plaats) LIKE '%o%'
ORDER BY plaats;

Geef het gemiddeld boetebedrag per speler, afgerond op twee cijfers na de komma. Spelers zonder boete krijgen als waarde ‘geen boetes’. Sorteer op spelersnaam.

Toon / verberg oplossing

SELECT spelers.naam,
  CASE
    WHEN AVG(boetes.bedrag) IS NULL THEN 'geen boetes'
    ELSE CAST(ROUND(AVG(boetes.bedrag), 2) AS varchar(8))
  END AS gemiddeld
FROM spelers LEFT OUTER JOIN boetes ON spelers.spelersnr = boetes.spelersnr
GROUP BY spelers.spelersnr, spelers.naam
ORDER BY 1

Geef het gemiddeld aantal gewonnen en verloren sets per geboortejaar. Rond telkens af op twee cijfers na de komma. Sorteer op geboortejaar zodat de gegevens van de jongste spelers bovenaan staan.

Toon / verberg oplossing

SELECT EXTRACT(YEAR FROM geb_datum) AS geboortejaar, ROUND(AVG(gewonnen),2) AS gewonnen,
  ROUND(AVG(verloren),2) AS verloren
FROM wedstrijden W INNER JOIN spelers S ON W.spelersnr = S.spelersnr
GROUP BY EXTRACT(YEAR FROM geb_datum)
ORDER BY geboortejaar DESC; 

Een moeilijke oefening … Geef voor de actieve bestuursleden zonder boete hun laatste gespeelde wedstrijd (die met het hoogste wedstrijdnummer). Sorteer aflopend op spelersnr.

Toon / verberg oplossing

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