2009

2 Toegepaste Informatica, reeks 1B 2009-2010 Databanken Opdracht: Oplossingen Oefeningen Student: Y. Reekmans

1

Oefensessie 18/09/2009 a. Maak een lijst van alle wedstrijden, die gespeeld werden voor het tweede team en die gewonnen zijn door een speler van onze ploeg (let op de kolomkoppen). Query SELECT wedstrijdnr, spelersnr, gewonnen as gewonnen_sets, verloren as verloren_sets FROM wedstrijden WHERE wedstrijden.teamnr = 2 AND gewonnen > verloren

b. Maak een overzicht waarbij je per woonplaats aangeeft hoeveel spelers er wonen in die gemeente. Query SELECT plaats, count(*) as aantal_per_plaats FROM spelers GROUP BY plaats ORDER BY plaats

Pas nu deze query zodanig aan dat je alleen die woonplaatsen overhoudt waar meer dan 2 spelers wonen. Bijkomend moet het resultaat aflopend geordend worden op het aantal spelers dat in eenzelfde woonplaats woont! Query SELECT plaats, count(*) as aantal_per_plaats FROM spelers GROUP BY plaats HAVING count(*) > 2 ORDER BY count(*) DESC

c. Maak een lijst van alle vrouwelijke aanvoerders van een team. Hierbij toon je voor deze spelers het spelersnummer en de volledige naam. Query SELECT t.spelersnr, s.naam, t.teamnr, t.divisie FROM spelers s, teams t WHERE t.spelersnr = s.spelersnr AND s.geslacht = ’V’

d. Geef het gemiddeld bedrag dat aan boetes betaald werd in het jaar 1980 (met als titel gemiddeld 1980), het totaal aantal boetes betaald in 1980 (met als titel totaal aantal boetes 1980) en het hoogste boetebedrag (met als titel hoogste boetebedrag 1980). Query SELECT AVG(bedrag) as gemiddeld_1980, COUNT(bedrag) as aantal_boetes, MAX(bedrag) as hoogste_bedrag FROM boetes WHERE extract(year from datum) = ’1980’

2009-2010

pg. 1 van 24

KHLeuven, dept. G&T, 2TI

e. Geef een lijst met het spelersnummer en de naam van de spelers uit Rijswijk die in de periode van 1980 tot en met 1985 meer dan n boete gekregen hebben. Query SELECT s.spelersnr, s.naam FROM spelers s, boetes b WHERE s.plaats = ’Rijswijk’ AND extract(year from b.datum) between ’1980’ and ’1985’ AND b.spelersnr = s.spelersnr GROUP BY s.spelersnr, s.naam HAVING count(*) > 2

f. Hoeveel spelers telt de ploeg? Query SELECT count(*) as aantal FROM spelers

g. Maak een lijst van alle mannelijke aanvoerders van een team. Bijkomend moet de naam van de speler de string tier bevatten en moet deze aanvoerder toegetreden zijn tot de club in de jaren 1977, 1978, 1979 of 1980. Query SELECT s.naam, t.teamnr, s.jaartoe FROM spelers s, teams t WHERE s.spelersnr = t.spelersnr AND jaartoe in(1977,1978,1979,1980) AND s.naam LIKE ’%tier%’

h. Hoeveel leden uit Den Haag, Rotterdam of Zoetermeer zetelen er momenteel in het bestuur maar zijn geen gewoon lid? Query SELECT count(*) FROM spelers s, bestuursleden b WHERE s.spelersnr = b.spelersnr AND b.functie <> ’Lid’ AND b. eind_datum is null AND s.plaats IN (’Zoetermeer’, ’Den Haag’, ’Rotterdam’)

i. Bereken voor alle spelers uit Den Haag hoeveel keer ze in het verleden in het bedstuur gezeteld hebben (hun huidige functie mag niet meegeteld worden). De lijst moet chronologisch geordend zijn op basis van de geboortedatum van de speler! Query SELECT s.spelersnr, s.naam, extract(year from s.geb_datum) as geboortejaar, COUNT(*) as aantal FROM spelers s, bestuursleden b WHERE b.spelersnr = s.spelersnr AND b.eind_datum is not null AND s.plaats= ’Den Haag’ GROUP BY s.spelersnr, s.naam, s.geb_datum ORDER BY geboortejaar ASC

j. Geef de naam en het spelersnummer van de spelers die ooit penningmeester geweest zijn van de club, die bovendien ooit een boete betaald hebben van meer dan 75 euro, en die ooit een wedstrijd gewonnen hebben met meer dan 2 sets verschil. Query SELECT s.naam, s.spelersnr FROM spelers s, bestuursleden b, boetes bo, wedstrijden w WHERE b.spelersnr = s.spelersnr AND bo.spelersnr = s.spelersnr AND w.spelersnr = s.spelersnr AND b.functie = ’Penningmeester’ AND bo.bedrag > 75 AND w.gewonnen-w.verloren > 2

2009-2010

pg. 2 van 24


2

Oefensessie 22/09/2009 a. Geef een lijst van alle reizen met ten minste n bezoek aan de Maan en/of aan Mars. Query SELECT DISTINCT r.reisnr, r.vertrekdatum FROM reizen r, hemelobjecten ho, bezoeken b WHERE b.reisnr = r.reisnr AND ho.objectnaam = b.objectnaam AND (ho.objectnaam = ’Maan’ OR ho.objectnaam = ’Mars’) AND b.verblijfsduur > 0

b. Welke reizigers hebben 2 of meer reizen ondernomen waarvoor ze meer dan 2,5 miljoen euro moesten betalen? Query SELECT k.naam, COUNT(*) as aantal_reizen FROM klanten k, deelnames d, reizen r WHERE k.klantnr = d.klantnr AND d.reisnr = r.reisnr AND r.prijs > 2.5 GROUP BY k.naam HAVING COUNT(*) >= 2

c. Welke planeten hebben meer dan 7 manen? Sorteer oplopend op basis van het aantal manen? Query SELECT ho.objectnaam, COUNT(*) as aantal_manen FROM hemelobjecten ho, hemelobjecten h WHERE ho.satellietVan = ’Zon’ AND ho.objectnaam = h.satellietVan GROUP BY ho.objectnaam HAVING COUNT(*) > 7 ORDER BY aantal_manen ASC

d. Bereken voor de klant wiens naam begint met M en eindigt met s hoeveel hij/zij in totaal al besteed heeft aan reizen. De tweede letter van de naam mag geen e zijn en de voorlaatste letter mag geen b zijn! Query SELECT k.naam, SUM(r.prijs) as bedrag, COUNT(d.reisnr) as aantal FROM klanten k, reizen r, deelnames d WHERE naam LIKE ’M%s’ AND naam NOT LIKE ’_e%b_’ AND d.reisnr = r.reisnr AND d.klantnr = k.klantnr GROUP BY k.naam

e. Ga op zoek naar alle reizen die vertrekken in de jaren 2020 tot en met 2025, waarvoor de prijs niet gekend is of als de prijs gekend is, moet hij tussen 50 en 75 miljoen euro liggen (opm. de waardes 50 en 75 miljoen euro mogen NIET meegeteld worden)! Query SELECT reisnr, extract(year from vertrekdatum) as jaartal, prijs FROM reizen WHERE extract(year from vertrekdatum) BETWEEN ’2020’ AND ’2025’ AND ((prijs > 50 AND prijs < 75) OR prijs is null)

f. Maak een lijst met de reizen die op een bepaald hemelobject langer verbleven zijn 8 dagen. Query SELECT reisnr, objectnaam FROM bezoeken WHERE verblijfsduur > 8

2009-2010

pg. 3 van 24


g. Ga per vertrekdatum van een reis, waarvoor je meer dan 10 000 000 euro moet betalen, op zoek naar hoeveel mensen die geboren zijn in de eerste helft van de jaren 70 zullen meegaan met deze reis. Op de uitvoer moet de vertrekdatum verschijnen en het aantal toeristen van die vertrekdatum die aan de voorwaarde voldoen. Query SELECT r.vertrekdatum, COUNT(*) as aantal_reizigers FROM reizen r, deelnames d, klanten k WHERE r.prijs > 10 AND d.reisnr = r.reisnr AND k.klantnr = d.klantnr AND extract(year from geboortedatum) BETWEEN ’1970’ AND ’1975’ GROUP BY r.vertrekdatum

h. Maak een lijst met een overzicht van de reizen en het aantal deelnemers van elke reis. Query SELECT reisnr, COUNT(*) FROM deelnames GROUP BY reisnr ORDER BY reisnr ASC

2009-2010

pg. 4 van 24


3

Oefensessie 29/09/2009 a. In vraag a van de vorige oefensessie ben je op zoek gegaan naar de reizen met ten minste n bezoek aan de Maan en/of aan Mars. Pas deze query nu zodanig aan dat je een lijst bekomt met al de ruimtereizen met ten minste n bezoek aan de Maan n aan Mars. Query SELECT DISTINCT r.reisnr, r.vertrekdatum FROM reizen r, bezoeken b, bezoeken be WHERE b.reisnr = r.reisnr AND be.reisnr = r.reisnr AND be.objectnaam = ’Maan’ AND b.objectnaam = ’Mars’ AND b.verblijfsduur > 0

b. Maak een lijst met de reizen die op een bepaald hemelobject langer verbleven zijn dan de maximum periode dat men ooit op de Maan verbleven heeft. Query SELECT reisnr, objectnaam FROM bezoeken WHERE verblijfsduur > (SELECT MAX(verblijfsduur) FROM bezoeken WHERE objectnaam = ’Maan’)

c. Maak een lijst met alle hemelobjecten waar ons reisbureau nog niet op bezoek geweest is of gepasseerd is en die een diameter hebben van meer dan 100.000 km. Sorteer de lijst aflopend volgens de grootte van de diameter. Query SELECT objectnaam, diameter FROM hemelobjecten WHERE diameter > 100000 AND objectnaam NOT IN (SELECT objectnaam FROM bezoeken)

d. Welke ruimtereizen heben een totale verblijfsduur op de bezochte hemelobjecten van ten minste 14 dagen? Query SELECT r.reisnr, r.reisduur, SUM(verblijfsduur) FROM reizen r, bezoeken b WHERE r.reisnr = b.reisnr GROUP BY r.reisnr, r.reisduur HAVING SUM(verblijfsduur) >= 14

e. Maak een lijst met klantgegevens van de personen die nog nooit op Phobos op bezoek geweest zijn. Query SELECT k.klantnr, k.naam, k.vnaam FROM klanten k WHERE k.klantnr NOT IN (SELECT d.klantnr FROM deelnames d, bezoeken b WHERE d.reisnr = b.reisnr AND b.objectnaam = ’Phobos’)

f. Op welke planeten verblijft men gemiddeld langer dan 2 dagen? Query SELECT b.objectnaam, AVG(verblijfsduur) FROM bezoeken b, hemelobjecten ho WHERE b.objectnaam = ho.objectnaam AND ho.satellietVan IN (SELECT objectnaam FROM hemelobjecten

2009-2010

pg. 5 van 24


WHERE satellietVan IS null) GROUP BY b.objectnaam HAVING AVG(verblijfsduur) > 2

g. Welke planeten hebben geen manen? Rangschik in alfabetische volgorde ! (let op, deze is niet zo evident !) Query SELECT objectnaam FROM hemelobjecten WHERE satellietVan IN (SELECT objectnaam FROM hemelobjecten WHERE satellietVan IS null) AND objectnaam NOT IN (SELECT DISTINCT satellietVan FROM hemelobjecten WHERE satellietVan IS NOT null) ORDER BY objectnaam ASC

2009-2010

pg. 6 van 24


4

Oefensessie 02/10/2009 a. Een klant is ge¨ınteresseerd in die reizen waarvoor geldt dat men minder dagen aan het vliegen is dan het totaal aantal dagen dat men op de verschillende hemelobjecten op bezoek is. Geef een overzicht van die reizen waar dit voor geldt, alsook het aantal dagen dat men in totaal onderweg is om de betrokken hemelobjecten te bezoeken. Query SELECT b.reisnr, (r.reisduur - SUM(b.verblijfsduur)) AS aantal_dagen_onderweg FROM bezoeken b, reizen r WHERE b.reisnr = r.reisnr GROUP BY b.reisnr, r.reisduur HAVING SUM(b.verblijfsduur) > (r.reisduur - SUM(b.verblijfsduur)) ORDER BY b.reisnr ASC

b. Maak een lijst met klantgegevens van de personen die nog nooit op Jupiter of op Io op bezoek geweest zijn. Je sorteert de mensen alfabetisch op basis van hun familienaam! Query SELECT klantnr, naam, vnaam FROM klanten WHERE klantnr NOT IN( SELECT k.klantnr FROM klanten k, deelnames d, reizen r, bezoeken b WHERE d.klantnr = k.klantnr AND r.reisnr = d.reisnr AND b.reisnr = d.reisnr AND (objectnaam = ’Io’ OR objectnaam = ’Jupiter’) AND verblijfsduur > 0 ) ORDER BY naam ASC

c. Maak een lijst van de klanten die meer dan 2 reizen ondernomen hebben! Query SELECT d.klantnr, k.naam, count(*) AS aantal_reizen FROM deelnames d, klanten k WHERE d.klantnr = k.klantnr GROUP BY d.klantnr, k.naam HAVING COUNT(*) > 2

d. Welke klanten gaan tijdens een reis op bezoek naar een satelliet van Jupiter? De namen moeten mooi samengesteld zijn in een combinatie van familienaam met voornaam, en deze namen moeten bovendien alfabetisch gesorteerd worden. Query SELECT k.naam || ’ ’ || k.vnaam as naam FROM bezoeken b, klanten k, deelnames d WHERE b.objectnaam IN( SELECT objectnaam FROM hemelobjecten WHERE satellietVan = ’Jupiter’ ) AND b.reisnr = d.reisnr AND k.klantnr = d.klantnr ORDER BY naam ASC

e. Welke reizen hebben exact drie hemelobjecten als reisdoel?

2009-2010

pg. 7 van 24


Query SELECT reisnr FROM bezoeken GROUP BY reisnr HAVING COUNT(objectnaam) = 3

f. Pas de bovenstaande query nu zodanig aan dat je een lijst bekomt met de reizen die exact drie verschillende hemelobjecten hebben als reisdoel ? Query SELECT reisnr FROM bezoeken GROUP BY reisnr HAVING COUNT(DISTINCT objectnaam) = 3

g. Probeer nu eens een tweede, totaal andere manier te vinden om deze zelfde vraag op te lossen! Query ??

h. In opgave b ging je op zoek naar alle mensen die nog nooit op Io of op Jupiter geweest zijn tijdens hun reizen. Hoe pas je deze query nu aan om ervoor te zorgen dat ze niet op Io geweest zijn en ook niet op Jupiter? Query SELECT klantnr, naam, vnaam FROM klanten WHERE klantnr NOT IN( SELECT k.klantnr FROM klanten k, deelnames d, reizen r, bezoeken b WHERE d.klantnr = k.klantnr AND r.reisnr = d.reisnr AND b.reisnr = d.reisnr AND objectnaam = ’Io’ AND verblijfsduur > 0 ) AND klantnr NOT IN( SELECT k.klantnr FROM klanten k, deelnames d, reizen r, bezoeken b WHERE d.klantnr = k.klantnr AND r.reisnr = d.reisnr AND b.reisnr = d.reisnr AND b.objectnaam = ’Jupiter’ AND b.verblijfsduur > 0 ) ORDER BY naam ASC

i. Maak een overzicht waarbij je voor de Maan en voor Mars aangeeft hoeveel ruimtereizen e´ e´ n of meer keer de betreffende bestemming bezocht hebben (d.w.z. erop geland zijn). Query SELECT objectnaam AS bestemming, COUNT(*) AS aantal_reizen FROM bezoeken WHERE objectnaam IN (’Maan’, ’Mars’) AND verblijfsduur > 0 GROUP BY objectnaam

j. Bereken voor alle hemelobjecten die satellieten hebben, het aantal satellieten per hemelobject. De lijst moet dalend gesorteerd worden op basis van het aantal satellieten van de hemelobjecten. (nvdr. moest je eraan twijfelen... deze oefening is niet echt evident...)

2009-2010

pg. 8 van 24


Query SELECT satellietVan AS hemelobject, count(*) AS aantal_satellieten FROM hemelobjecten WHERE satellietVan is not null GROUP BY satellietVan ORDER BY aantal_satellieten DESC

2009-2010

pg. 9 van 24


5

Oefensessie 06/10/2009 a. Maak een lijst van alle wedstrijden, die gespeeld werden voor het tweede team en die gewonnen zijn door een speler van onze ploeg (let op de kolomkoppen). Query SELECT w.wedstrijdnr, w.spelersnr, w.gewonnen as gewonnen_sets, w.verloren as verloren_sets FROM wedstrijden w WHERE w.teamnr = 2 AND w.gewonnen > w.verloren

b. Maak een lijst van al de spelers met de boetes die deze speler gekregen heeft. Indien een speler geen boete gekregen heeft moet hij/zij eveneens op de lijst verschijnen maar op de plaats waar het boetebedrag moet verschijnen moet hier null komen. Bovendien moet de naam van de speler beginnen met Ba en moet de voorlaatste letter van zijn/haar naam een e zijn. (Denk eraan dat het Nederlandse gewoonte is om het tussenvoegsel na een komma toe te voegen aan de familienaam!) Sorteer de lijst oplopend op basis van de naam van de speler en voor spelers met dezelfde naam, aflopend op basis van het boetebedrag! Query SELECT spelersnr, naam, bedrag FROM spelers LEFT JOIN boetes USING (spelersnr) WHERE naam LIKE ’Ba%e_’ OR naam LIKE ’Ba%e_, %’ ORDER BY naam ASC, bedrag DESC

c. Geef de naam en het spelersnummer van de spelers die ooit penningmeester geweest zijn van de club, die bovendien ooit een boete betaald hebben van meer dan 75 euro, en die ooit een wedstrijd gewonnen hebben met meer dan 2 sets verschil Gebruik 3 verschillende manieren om dit antwoord te verkrijgen (nl. expliciete join, impliciete join en subqueries). Query (Expliciete Join) SELECT spelersnr, naam FROM spelers JOIN bestuursleden USING(spelersnr) JOIN boetes USING(spelersnr) JOIN wedstrijden USING(spelersnr) WHERE functie = ’Penningmeester’ AND bedrag > 75 AND gewonnen-verloren > 2 Query (Impliciete Join) SELECT s.spelersnr, naam FROM spelers s, bestuursleden bl, boetes b, wedstrijden w WHERE s.spelersnr = bl.spelersnr AND bl.spelersnr = b.spelersnr AND b.spelersnr = w.spelersnr AND functie = ’Penningmeester’ AND bedrag > 75 AND gewonnen-verloren > 2 Query (Subqueries) SELECT spelersnr, naam FROM spelers WHERE spelersnr IN ( SELECT spelersnr FROM bestuursleden WHERE functie = ’Penningmeester’ AND spelersnr IN( SELECT spelersnr FROM boetes WHERE bedrag > 75 AND spelersnr IN(

2009-2010

pg. 10 van 24


SELECT spelersnr FROM wedstrijden WHERE gewonnen-verloren > 2 ) ) )

d. Geef de spelers (nummer en naam) die ouder zijn dan B. Niewenburg Query SELECT s.spelersnr, s.naam FROM spelers s, spelers bn WHERE s.geb_datum < bn.geb_datum AND bn.naam = ’Niewenburg’ AND bn.voorletters = ’B’

e. Maak een lijst met het spelersnummer en de naam van de spelers uit Rotterdam of Leiden, die minder dan 5 wedstrijden gespeeld hebben en nooit een boete gekregen hebben. Spelers die nog niet gespeeld hebben (en dus logischerwijze ook geen boete gekregen hebben) moeten eveneens op het resultaat verschijnen! N.v.d.r. Cools heeft wel degelijk 0 wedstrijden gespeeld, hier moet dus effectief een 0 verschijnen! Query SELECT spelersnr, naam, COUNT(w.spelersnr) FROM spelers LEFT JOIN wedstrijden w USING(spelersnr) WHERE plaats IN(’Rotterdam’, ’Leiden’) AND spelersnr NOT IN( SELECT spelersnr FROM boetes ) GROUP BY spelersnr, naam HAVING COUNT(w.spelersnr) < 5

f. Geef de naam en het spelersnummer van de spelers die in n wedstrijd meer sets gewonnen hebben dan speler 2 in totaal aan sets gewonnen heeft. Query SELECT DISTINCT naam, spelersnr FROM spelers JOIN wedstrijden USING(spelersnr) WHERE gewonnen > (SELECT SUM(gewonnen) FROM wedstrijden WHERE spelersnr = 2) ORDER BY spelersnr ASC

g. Geef het totaal aantal boetes, het totale boetebedrag, het minimum en het maximum boetebedrag dat door onze club betaald werd. Query SELECT COUNT(*) as AANTAL_BOETES, SUM(bedrag) as TOTAAL_BEDRAG, MIN(bedrag) as MINIMUM, MAX(bedrag) as MAXIMUM FROM boetes

h. Geef per divisie van de teams het totaal aantal gewonnen en het totaal aantal verloren sets (je mag ervan uitgaan dat elk team reeks 1 of meerdere wedstrijden gespeeld heeft). Query SELECT divisie, SUM(gewonnen) as TOTAAL_GEWONNEN, SUM(verloren) as TOTAAL_VERLOREN FROM teams JOIN wedstrijden USING(teamnr) GROUP BY divisie

2009-2010

pg. 11 van 24


Hoe pas je deze query nu aan om ook de divisies waar nog geen team voor gespeeld heeft op te nemen in het resultaat? Query SELECT divisie, SUM(gewonnen) as TOTAAL_GEWONNEN, SUM(verloren) as TOTAAL_VERLOREN FROM teams LEFT JOIN wedstrijden USING(teamnr) GROUP BY divisie

2009-2010

pg. 12 van 24


6

Oefensessie 09/10/2009 a. Geef per speler die momenteel in het bestuur zetelt, de som van de boetes die door deze speler betaald werden voor 1990. Bovendien moet het resultaat stijgend gesorteerd worden volgens het totale boetebedrag. Query SELECT spelersnr, naam, COUNT(bedrag) as AANTAL, SUM(bedrag) as TOTAAL_BEDRAG FROM bestuursleden JOIN boetes USING(spelersnr) JOIN spelers USING(spelersnr) WHERE eind_datum IS NULL AND extract(YEAR from datum) < 1990 GROUP BY spelersnr, naam ORDER BY totaal_bedrag ASC

b. Geef de spelers (woonplaats, naam, geslacht, in volgorde van hun geslacht en naam) voor wie minstens n boete betaald werd maar die geen aanvoerder zijn van een team. Query SELECT geslacht, naam, plaats FROM spelers s, boetes b WHERE s.spelersnr = b.spelersnr AND s.spelersnr NOT IN( SELECT spelersnr FROM teams ) GROUP BY geslacht, naam, plaats HAVING COUNT(bedrag) >= 1 ORDER BY geslacht, naam

c. Geef het aantal verschillende spelers dat ooit een wedstrijd gespeeld heeft. Query SELECT COUNT(DISTINCT spelersnr) as AANTAL_SPELERS FROM wedstrijden

d. Welke speler(s) hebben ooit het maximum boetebedrag, dat ooit betaald werd, betaald ? Sorteer alfabetisch op basis van de familienaam. Query SELECT naam, voorletters FROM spelers JOIN boetes USING(spelersnr) WHERE bedrag = ( SELECT MAX(bedrag) FROM boetes ) ORDER BY naam

e. Geef een lijst van al de spelers die in Zoetermeer of Amsterdam wonen (nummer, naam, woonplaats en geslacht waarbij je als geslacht man of vrouw of verkeerd geslacht geeft). Query SELECT spelersnr, naam, plaats, case when geslacht = ’V’ then ’Vrouw’ when geslacht = ’M’ then ’Man’ else ’Verkeerd geslacht’ end as geslacht FROM spelers WHERE plaats IN(’Amsterdam’, ’Zoetermeer’) Query SELECT spelersnr, naam, plaats, case geslacht when ’V’ then ’Vrouw’

2009-2010

pg. 13 van 24


when ’M’ then ’Man’ else ’Verkeerd geslacht’ end as geslacht FROM spelers WHERE plaats IN(’Amsterdam’, ’Zoetermeer’)

f. Welke speler heeft in totaal een boetebedrag dat dubbel zo hoog is als het totale bedrag van speler 104? Query SELECT spelersnr, naam, straat || ’ ’ || huisnr || ’ ’ || postcode || ’ ’ || plaats as volledig_adres FROM spelers JOIN boetes USING(spelersnr) GROUP BY spelersnr, naam, straat, huisnr, postcode, plaats HAVING SUM(bedrag) = 2*( SELECT SUM(bedrag) FROM boetes WHERE spelersnr = 104 )

g. En welke speler(s) hebben evenveel boetes betaald dan Niewenburg B uit Rijswijk? Query SELECT spelersnr, naam FROM spelers JOIN boetes USING(spelersnr) GROUP BY spelersnr, naam HAVING COUNT(spelersnr) = ( SELECT COUNT(spelersnr) FROM boetes JOIN spelers USING(spelersnr) WHERE naam = ’Niewenburg’ AND voorletters = ’B’ ) AND spelersnr <> ( SELECT spelersnr FROM spelers WHERE naam = ’Niewenburg’ AND voorletters = ’B’ )

h. Bereken per speler het totaal bedrag aan boetes dat deze speler betaald heeft. Van de speler moet je zijn/haar nummer en naam weergeven, samen met het berekende totaal. Query SELECT spelersnr, naam, SUM(bedrag) FROM spelers JOIN boetes USING(spelersnr) GROUP BY spelersnr, naam ORDER BY spelersnr

Voordat je begint te twijfelen aan de docent in kwestie en het feit of deze oefeningen nu toch echt niet een beetje heel simpel worden, de volgende vraag : pas deze query zodanig aan dat je enkel die spelers weergeeft met hun totale boetebedrag indien die speler meer dan 2 wedstrijden gespeeld heeft. Query SELECT spelersnr, naam, SUM(bedrag) FROM spelers s JOIN boetes USING(spelersnr) GROUP BY spelersnr, naam HAVING (SELECT COUNT(spelersnr) FROM wedstrijden w WHERE w.spelersnr = s.spelersnr) > 2 ORDER BY spelersnr

2009-2010

pg. 14 van 24


Query SELECT spelersnr, naam, SUM(bedrag) FROM spelers s JOIN boetes USING(spelersnr) GROUP BY spelersnr, naam HAVING (SELECT COUNT(spelersnr) FROM wedstrijden w WHERE w.spelersnr = s.spelersnr) > 2 AND spelersnr IN( SELECT spelersnr FROM teams ) ORDER BY spelersnr

i. En natuurlijk wil je nu nog weten hoe je erin kan slagen om bij het boetebedrag ook het aantal wedstrijden dat de speler gespeeld heeft en het aantal teams waar hij/zij aanvoerder van is, te laten verschijnen. Doe gerust ! Query SELECT spelersnr, naam, SUM(bedrag) as TOTAAL, (SELECT COUNT(spelersnr) FROM wedstrijden w WHERE w.spelersnr = s.spelersnr) as A_WEDSTR, (SELECT COUNT(teamnr) FROM teams t WHERE t.spelersnr = s.spelersnr) as A_TEAMS FROM spelers s LEFT JOIN boetes USING(spelersnr) GROUP BY spelersnr, naam ORDER BY spelersnr

Kijk, en nu wil je alleen nog alleen die rijen overhouden waarvoor geldt dat de speler minstens een totale boete van 50 euro gehad heeft, minstens 1 wedstrijd gespeeld heeft en daarenboven aanvoerder is van een team! Laat me er voor alle duidelijkheid even bijschrijven dat deze oefening onverantwoord moeilijk is en alleen door de echte masochisten opgelost moet worden (psychologische bijstand kan voorzien worden) Query SELECT spelersnr, naam, SUM(bedrag) as TOTAAL, (SELECT COUNT(spelersnr) FROM wedstrijden w WHERE w.spelersnr = s.spelersnr) as A_WEDSTR, (SELECT COUNT(teamnr) FROM teams t WHERE t.spelersnr = s.spelersnr) as A_TEAMS FROM spelers s LEFT JOIN boetes USING(spelersnr) GROUP BY spelersnr, naam HAVING (SELECT COUNT(teamnr) FROM teams t WHERE t.spelersnr = s.spelersnr) >= 1 AND SUM(bedrag) >= 50 AND (SELECT COUNT(spelersnr) FROM wedstrijden w WHERE w.spelersnr = s.spelersnr) >= 1 ORDER BY spelersnr

2009-2010

pg. 15 van 24


7

Oefensessie 13/10/2009 a. In oefening b van de vorige oefeningenreeks ben je op zoek gegaan naar alle spelers (woonplaats, naam, geslacht, in volgorde van hun geslacht en naam) voor wie minstens n boete betaald werd maar die geen aanvoerder zijn van een team. Je hebt die oefening toen opgelost m.b.v. een subquery! Probeer nu een andere oplossing uit te werken die gebruik maakt van de combinatie van queries! Query SELECT geslacht, naam, plaats FROM spelers s, boetes b WHERE s.spelersnr = b.spelersnr GROUP BY geslacht, naam, plaats HAVING COUNT(bedrag) >= 1 EXCEPT SELECT geslacht, naam, plaats FROM spelers s, teams t WHERE s.spelersnr = t.spelersnr ORDER BY geslacht, naam

b. Maak een overzicht waarbij je per geslacht aangeeft hoeveel mannelijke en hoeveel vrouwelijke spelers er zijn die in Den Haag of Zoetermeer wonen. Query SELECT geslacht, COUNT(geslacht) FROM spelers WHERE plaats IN(’Zoetermeer’, ’Den Haag’) GROUP BY geslacht

c. Geef chronologisch de spelersnummers van de bestuursleden die voorzitter zijn of geweest zijn (chronologisch op begindatum van het voorzitterschap) met vermelding van deze begindatum, alsook hun naam en huidig adres. Als het adres niet gekend is (m.a.w. gemeente is onbekend) dan moet adres ongekend weergegeven worden. Probeer ook deze oefening op te lossen met een combinatie van queries ! Query SELECT begin_datum, naam, case when plaats IS NULL then ’adres onbekend’ else straat || ’ ’ || huisnr || ’ ’ || postcode || ’ ’ || plaats end as adres FROM bestuursleden JOIN spelers USING(spelersnr) WHERE functie = ’Voorzitter’ ORDER BY begin_datum ASC

d. Ook deze oefening komt je waarschijnlijk bekend voor : geef een lijst van al de spelers die in Zoetermeer of Amsterdam wonen (nummer, naam, woonplaats en geslacht waarbij je als geslacht man of vrouw of verkeerd geslacht geeft). Als je deze opgave nu moet oplossen zonder de case instructie, hoe slaag je er dan in? Query ??

e. Maak een lijst met alle vrouwelijke spelers uit Den Haag, Zoetermeer, Rotterdam of Leiden die minder dan 3 wedstrijden gespeeld hebben! Sorteer de lijst alfabetisch op naam!

2009-2010

pg. 16 van 24


Query SELECT naam, ( SELECT COUNT(wedstrijdnr) FROM wedstrijden w WHERE w.spelersnr = s.spelersnr ) as AANTAL_WEDSTRIJDEN FROM spelers s WHERE geslacht = ’V’ AND plaats IN(’Leiden’, ’Den Haag’, ’Zoetermeer’, ’Rotterdam’) GROUP BY spelersnr, naam HAVING ( SELECT COUNT(wedstrijdnr) FROM wedstrijden w WHERE w.spelersnr = s.spelersnr ) < 3 ORDER BY naam Query SELECT naam, ( SELECT COUNT(wedstrijdnr) FROM wedstrijden w WHERE w.spelersnr = s.spelersnr AND w.teamnr = 2 ) as AANTAL_WEDSTRIJDEN FROM spelers s WHERE geslacht = ’V’ AND plaats IN(’Leiden’, ’Den Haag’, ’Zoetermeer’, ’Rotterdam’) GROUP BY spelersnr, naam HAVING ( SELECT COUNT(wedstrijdnr) FROM wedstrijden w WHERE w.spelersnr = s.spelersnr AND w.teamnr = 2 ) < 3 ORDER BY naam Query SELECT naam, ( SELECT COUNT(wedstrijdnr) FROM wedstrijden w WHERE w.spelersnr = s.spelersnr AND w.teamnr = 2 ) as AANTAL_WEDSTRIJDEN FROM spelers s WHERE geslacht = ’V’ AND plaats IN(’Leiden’, ’Den Haag’, ’Zoetermeer’, ’Rotterdam’) GROUP BY spelersnr, naam HAVING ( SELECT COUNT(wedstrijdnr) FROM wedstrijden w WHERE w.spelersnr = s.spelersnr AND w.teamnr = 2 ) < 3 ORDER BY naam Query

f. SELECT s.spelersnr, naam, SUM(bedrag) as totaal_bedrag FROM spelers as s, boetes as b GROUP BY s.spelersnr, naam, bedrag HAVING bedrag > 25 AND SUM(bedrag) < 200

2009-2010

pg. 17 van 24


8

Oefensessie 16/10/2009 a. Maak een overzicht waarbij je voor de Maan en voor Mars aangeeft hoeveel ruimtereizen e´ e´ n of meer keer de betreffende bestemming bezocht hebben (d.w.z. erop geland zijn). Query SELECT objectnaam, COUNT(reisnr) FROM bezoeken WHERE objectnaam IN(’Maan’, ’Mars’) AND verblijfsduur > 0 GROUP BY objectnaam

b. Maak een lijst met een overzicht van de reizen. Op het resultaat moet het reisnummer verschijnen, het aantal verschillende hemelobjecten dat tijdens deze reis zal bezocht worden en de prijs van deze reis. Indien de prijs nog niet gekend is, moet er in de kolom van de prijs prijs nog niet gekend afgedrukt worden. Query SELECT reisnr, COUNT(DISTINCT objectnaam) as aantal_te_bezoeken, case when prijs is null then ’prijs nog niet gekend’ else CAST(prijs as text) end as prijs FROM reizen JOIN bezoeken USING(reisnr) GROUP BY reisnr, prijs

c. Maak een lijst van de klanten die in 2021 niet op reis gaan! Sorteer de namenlijst alfabetisch op basis van de familienaam! Query SELECT naam, vnaam FROM klanten WHERE klantnr NOT IN( SELECT klantnr FROM deelnames JOIN reizen USING(reisnr) WHERE extract(YEAR from vertrekdatum) = 2021 ) ORDER BY naam

d. Maak een lijst met een overzicht van de reizen en het aantal deelnemers van elke reis. Query SELECT reisnr, COUNT(klantnr) as deelnemers FROM deelnames GROUP BY reisnr ORDER BY reisnr

e. Pas query d zodanig aan dat ook de ruimtereizen waarvoor (nog) geen deelnemers zijn ingeschreven, in het overzicht verschijnen. In deze gevallen moet de waarde voor het aantal deelnemers niet ingevuld zijn. Dan pas je de query zodanig aan dat als er geen deelnemers zijn je 0 toont. Query SELECT reisnr, COUNT(klantnr) as deelnemers FROM deelnames RIGHT JOIN reizen USING(reisnr) GROUP BY reisnr ORDER BY reisnr

f. Welke manen hebben een grotere diameter dan alle Neptunusmanen? Sorteer het resultaat alfabetisch op basis van de planeet. 2009-2010

pg. 18 van 24


Query SELECT objectnaam as Maan, satellietVan as Planeet FROM hemelobjecten WHERE satellietVan IS NOT NULL AND satellietVan <> ’Zon’ AND diameter > (SELECT MAX(diameter) FROM hemelobjecten WHERE satellietVan = ’Neptunus’ )

g. Wat is het resultaat (zonder het uit te testen natuurlijk ) van de volgende query ? Query SELECT reisnr, prijs FROM reizen r WHERE prijs IS NOT NULL AND 3 > (SELECT COUNT(*) FROM reizen rz WHERE rz.prijs > r.prijs) ORDER BY prijs Query Resultaat

Deze query geeft alle reizen terug waarvan de prijs bekend is en er minder dan 3 reizen zijn die duurder zijn dan deze reis, dwz. de drie goedkoopste reizen worden weergegeven.

2009-2010

pg. 19 van 24


9

Oefensessie 20/10/2009 a. Maak een lijst met de dieren waarvoor geldt dat van dit dier meer foto’s genomen zijn dan van alle stokstaartjes samen! Op het resultaat moet het nummer van het dier en zijn naam verschijnen, samen met het aantal foto’s die genomen werden van dat dier! De lijst moet dalend gesorteerd worden op basis van het berekende aantal (dat de titel aantal moet krijgen), dieren die een gelijk aantal gefotografeerd werden moeten alfabetisch gesorteerd worden! Query SELECT dierid, naam, COUNT(*) as aantal FROM opnamesdieren JOIN dieren USING(dierid) GROUP BY dierid, naam HAVING COUNT(*) > (SELECT COUNT(*) FROM opnamesdieren WHERE dierid IN(SELECT dierid FROM dieren WHERE soort = ’Stokstaartje’ ) ) ORDER BY aantal DESC, naam ASC

b. Maak een lijst waarbij je voor elk dier, waar foto’s van genomen werden, telt hoeveel foto’s genomen werden en wat de totale prijs is die voor die foto’s betaald werd. Enkel de foto’s waar een prijs voor bepaald werd, mogen meegeteld worden. Het resultaat waar je de nummer van het dier en zijn/haar naam weergeeft, samen met het aantal foto’s en de totale prijs (voor deze laatste kolommen kies je zelf een betekenisvolle en gebruikersvriendelijke naam) moet dalend gesorteerd worden op basis van het aantal opnames en de totale prijs die hiervoor betaald werd! Query SELECT dierid, naam, COUNT(dierid) as aantal_opnames, SUM(prijs) as totale_prijs FROM opnamesdieren JOIN opnames USING(opnameid) JOIN dieren USING(dierid) WHERE prijs IS NOT null GROUP BY dierid, naam ORDER BY aantal_opnames DESC, totale_prijs DESC

c. Maak een alfabetisch lijst van alle dieren waarvoor minder dan 2 foto’s genomen werden, natuurlijk moeten de dieren waar totnogtoe geen foto’s van genomen werden, ook op de lijst verschijnen! Op de lijst moet de naam van het dier verschijnen en het aantal foto’s, dit veld moet de titel aantal opnames krijgen. Query SELECT dierid, naam, COUNT(opnamedierid) as aantal_opnames FROM opnamesdieren RIGHT JOIN dieren USING(dierid) GROUP BY dierid, naam HAVING COUNT(opnamedierid) < 2 ORDER BY naam ASC

d. Maak een lijst waarbij je voor alle vrouwelijke olifanten die geen kinderen hebben, telt op hoeveel foto’s deze olifant voorkomt. Dieren die aan de voorwaardes voldoen maar op geen enkele foto voorkomen, moeten eveneens op de lijst verschijnen met een aantal gelijk aan 0. Op de uitvoer moet de naam van de olifant verschijnen, samen met haar geboortejaar en het berekende aantal. Deze laatste kolom moet de titel aantal opnames krijgen! Bijkomend moet de lijst dalend gesorteerd worden op basis van het berekende aantal. 2009-2010

pg. 20 van 24


Query SELECT dierid, geboortejaar, COUNT(opnamedierid) as aantal_opnames FROM dieren LEFT JOIN opnamesdieren USING(dierid) WHERE soort = ’Olifant’ AND geslacht = ’v’ AND dierid NOT IN(SELECT dieridouder FROM kindouder) GROUP BY dierid, geboortejaar ORDER BY aantal_opnames DESC

e. In welke steden vond nog nooit een fotosessie plaats en werd er nog nooit een dier geboren! Als resultaat geef je de steden en de bijhorende landen. Tip: houd er rekening mee dat de plaats waar een dier geboren is, onbekend kan zijn!! Query SELECT plaatsnaam, land FROM plaatsen WHERE plaatsnaam NOT IN(SELECT plaatsnaam FROM periodes) AND plaatsnaam NOT IN(SELECT geboorteplaats FROM dieren WHERE geboorteplaats IS NOT NULL)

f. Tel het verschillend aantal soorten dieren dat verschenen is op opnames waarvoor een prijs bepaald werd maar waarvoor minder dan 400 euro betaald werd! Query SELECT COUNT(DISTINCT soort) as aantal_soorten FROM opnamesdieren JOIN dieren USING(dierid) WHERE opnameid IN (SELECT opnameid FROM opnames WHERE prijs < 400)

2009-2010

pg. 21 van 24


10

Oefensessie 27/10/2009

a. Van welke dieren werd ooit een foto genomen op dezelfde plaats als hun geboorteplaats? Dubbele rijen mogen niet weergegeven worden. Query SELECT DISTINCT naam, plaatsnaam FROM dieren JOIN opnamesdieren USING(dierid) JOIN opnames USING(opnameid) JOIN periodes ON datum BETWEEN begindatum AND einddatum WHERE geboorteplaats = plaatsnaam

b. Maak een view met een overzicht van alle dieren waarvoor meer dan 2 foto’s genomen werden. Op de lijst moet de naam van het dier verschijnen en het aantal foto’s, dit veld moet de titel aantal opnames krijgen. Je geeft deze view een naam naar keuze! Query CREATE VIEW naam_opnames AS SELECT naam, COUNT(opnamedierid) as aantal_opnames FROM opnamesdieren RIGHT JOIN dieren USING(dierid) GROUP BY naam HAVING COUNT(opnamedierid) > 2

Controleer de inhoud van de view door een query die gewoon alle gegevens van de view opvraagt! Query SELECT * FROM naam_opnames

Vervolgens gebruik je deze view om de gegevens (de naam en het aantal foto’s) van Johanna op te vragen! Query SELECT * FROM naam_opnames WHERE naam = ’Johanna’

Vervolgens gebruik je deze zelfde view om te berekenen hoeveel dieren er zijn waarvan meer dan 3 foto’s genomen werden! Query SELECT COUNT(*) as aantal FROM naam_opnames WHERE aantal_opnames > 3

c. Van hoeveel verschillende soorten dieren die geboren zijn tussen 1940 en 1960 (deze jaren moeten meegeteld worden) en waarvan men de geboorteplaats kent, zijn er foto’s getrokken? Query SELECT COUNT(DISTINCT soort) FROM opnamesdieren JOIN dieren USING(dierid) WHERE dierid IN(SELECT dierid FROM dieren WHERE geboortejaar BETWEEN 1940 AND 1960 AND geboorteplaats IS NOT null)

2009-2010

pg. 22 van 24


d. Welke dieren (naam, soort en geboortejaar van het dier moeten getoond worden) die geboren zijn in Frankrijk, Spanje of Nederland zijn ouder dan de panter met naam Marie. Let op: de naam van de gezochte dieren moet beginnen met een H. Query SELECT naam, soort, geboortejaar FROM dieren JOIN plaatsen ON(plaatsnaam = geboorteplaats) WHERE naam LIKE ’H%’ AND land IN(’Spanje’, ’Frankrijk’, ’Nederland’) AND geboortejaar < (SELECT geboortejaar FROM dieren WHERE naam = ’Marie’ AND soort = ’Panter’)

e. Maak een lijst van alle diersoorten die met een olifant op dezelfde foto staan. Als het andere dier ook een olifant is, moet je dit dier niet weergeven! Op het resultaat moeten alle diersoorten (indien dezelfde soort meerdere keren voorkomt, mag het maar e´ e´ nmaal getoond worden) in alfabetische volgorde verschijnen! Query SELECT DISTINCT soort FROM opnamesdieren JOIN dieren USING(dierid) WHERE soort <> ’Olifant’ AND opnameid IN( SELECT opnameid FROM opnamesdieren WHERE dierid IN( SELECT dierid FROM dieren WHERE soort = ’Olifant’ ) ) ORDER BY soort ASC

Los deze zelfde vraag nu op door eerst een view te maken met alle opnamenummers waar een Olifant op staat en vervolgens gebruik je deze view in je query om uiteindelijk natuurlijk hetzelfde resultaat te bekomen! Query CREATE VIEW olifantenfotos AS SELECT opnameid FROM opnamesdieren WHERE dierid IN( SELECT dierid FROM dieren WHERE soort = ’Olifant’ ) Query SELECT DISTINCT soort FROM opnamesdieren JOIN dieren USING(dierid) WHERE soort <> ’Olifant’ AND opnameid IN( SELECT opnameid FROM olifantenfotos ) ORDER BY soort ASC

f. Welk is het oudste dier (geef de naam en het soort)?

2009-2010

pg. 23 van 24


Query SELECT naam, soort FROM dieren WHERE geboortejaar = (SELECT MIN(geboortejaar) FROM dieren)

g. Van welke vrouwelijke dieren, die als moeder geregistreerd staan voor een ander dier, werd nog nooit een foto genomen! Als resultaat geef je de naam en het soort van deze dieren! Query SELECT naam, soort FROM dieren JOIN kindouder ON(dierid = dieridouder) WHERE geslacht = ’v’ AND dierid NOT IN(SELECT dierid FROM opnamesdieren)

2009-2010

pg. 24 van 24


2009

Recommend Documents