Hoofdstuk 7
7
Databases
7.1
Databases
Na verwerking van dit hoofdstuk: kun je uitleggen wat een database systeem is; ken je de verschillen tussen een één-op-één relatie, een één-op-veel relatie en een veel-op-veel relatie; weet je wat een relationele database is; ken je de begrippen record, sleutel, redundantie, inconsistentie; weet je wat SQL is; kun je bij een gepresenteerde database een strokendiagram maken; kun je eenvoudige selecties uit enkele tabellen in een database maken met SQL; kun je werken met samengestelde voorwaarden in SQL; kun je eenvoudige selecties maken uit een database door tabellen te combineren; kun je functies gebruiken in SQL; kun je rijen op basis van overeenkomsten groeperen in SQL; kun je omgaan met subquery‟s; kun je in een lege database een tabel aanmaken en daarin gegevens toevoegen en/of verwijderen.
1 RELATIONELE DATABASES
VERWERKEN 1 In het informatieboek staan de volgende termen: database, database management systeem en database systeem. Welke termen horen bij: a Het programma MS-Access? b De ledenlijst van biljartvereniging „Tien Over Rood‟? c De ledenadministratie van de bibliotheek? d De helpfunctie in een spelletjesprogramma? 2 In het programma Word zit ook een database verwerkt. a Noem vier soorten gegevens die daarin zijn opgeslagen. b Wat is het verschil tussen een database in Word en een door jou zelf gebouwde database met de gegevens van al je cd‟s? c Geef drie voorbeelden van databases waarin je via het World Wide Web kunt zoeken. 3 Bekijk de hiërarchische modellen. a Wat voor soort relatie is mentor – klas? b Wat voor soort relatie is mentor – leerling? c Wat voor soort relatie is leerlingen – vakken? d Wat voor soort relatie is docenten – vakken? 4 Bekijk de informatieboek de tabel leerlingen. a Hoeveel veldnamen kom je tegen in deze tabel?
ENIGMA
7.2 b c d e
Verwerkingsboek
„Vertaal‟ de derde rij in de tabel naar een gewone Nederlandse zin. Op welke veldnaam is de tabel gesorteerd? Uit hoeveel records bestaat de tabel? Hoeveel velden heeft iedere record? Er is één kolom waar ieder gegeven maximaal één keer kan voorkomen. Welke kolom is dat? Waarom krijgt iedere leerling een administratienummer?
f 5 In a b c
het informatieboek lees je dat alle velden van één kolom behoren tot hetzelfde type. Van welk type zijn de gegevens in de eerste kolom? En in de kolom Adres? Deze tabel is nog niet compleet. Welke kolommen zouden er volgens jou nog moeten worden toegevoegd?
6 In de leerlingentabel is Admnr de sleutel waarmee iedere leerling uniek geïdentificeerd kan worden. a Waar dient een sleutel voor? b Men had er ook voor kunnen kiezen om de combinatie Voornaam – Achternaam – Adres – Woonplaats als sleutel te gebruiken. Waarom wordt dat niet gedaan? 7 In het dagelijkse leven is er vaak sprake van een unieke identificatie door middel van een sleutel. Denk daarbij bijvoorbeeld aan overheidsdiensten. a Noem drie voorbeelden. b Auto‟s kunnen op 2 manieren uniek geïdentificeerd worden, dus met 2 verschillende sleutels. Welke sleutels zijn dat en door wie of welke instantie worden ze gebruikt? c Met welke sleutel worden bromfietsen uniek geïdentificeerd? 8 In het informatieboek staat: “Om alle absenties te kunnen vastleggen, zijn in deze tabel dezelfde gegevens meerdere keren opgenomen.” a Leg uit wat bedoeld wordt. b Waar is sprake van wanneer dezelfde informatie meer dan één keer in de tabel staat? c Wat is daarvan een gevaar? d Noem een mogelijke oplossing. 9 Bekijk de tabellen Leerlingen en Absentie. a Wat zijn de voor- en achternaam van de leerling die op 13 november 2000 afwezig was? b Welke leerling was drie dagen afwezig? c Wat is de sleutel van de tabel Absentie?
TOEPASSEN 10 Een bibliotheek bezit boeken, cd‟s en dvd‟s. De boeken zijn ingedeeld in de rubrieken Fictie en Non-fictie, de cd‟s in de rubrieken Klassiek en Lichte Muziek en de dvd‟s in de rubrieken Drama, Actie, Humor en Jeugdfilms. De rubriek Fictie is weer onderverdeeld in Copyright © 2007 - 2011
ENIGMA-online
Hoofdstuk 7
Databases
7.3
Nederlands en Buitenlands. Lichte Muziek kent de categorieën Pop, Jazz en Volksmuziek terwijl de rubriek Jeugdfilms nog is verdeeld in Speelfilms en Tekenfilms. a Teken een hiërarchisch model van de bibliotheek. Aan de „wortel‟ van de boomstructuur staat „Collectie‟. b Maak een tabelletje waarin alle relaties zijn onderverdeeld in één-op-één relaties en één-op-veel relaties. 11 Bekijk in het informatieboek het hiërarchisch model van leerlingen en hun vakdocenten a Maak een soortgelijk model voor 8 van je klasgenoten en 4 vakken met docenten b Wat blijkt nu het grote nadeel van deze benadering te zijn?
VERDIEPEN
12 Digitale opslagmedia kennen ook een hiërarchische structuur. Welk soort relatie komt hierin niet voor? Leg uit waarom. 13 Wanneer dezelfde informatie meer dan één keer in een tabel staat, is sprake van redundantie. a Zoek in een woordenboek het begrip „redundantie‟ op en leg de betekenis hiervan uit in de context van de betreffende alinea in het informatieboek. b In de absentietabel alleen kun je niet zien wie er op een bepaalde dag afwezig was. Beschrijf wat je gedaan hebt om hier achter te komen. c In welke klas zijn geen absenten geweest? 14 Een sleutel moet uniek identificeren. a Is de pincode van jouw bank- of giropasje een sleutel? b Waarom wel of waarom niet? c Verklaar deze term aan de hand van de behandelde tabellen. 15 Als er in een tabel moet worden bepaald welke kolom of combinatie van kolommen het meest geschikt is als sleutel, zoekt men in het algemeen eerst naar een aantal „kandidaatssleutels‟. a Geef van de volgende tabel Debiteuren de kandidaatssleutels en kies vervolgens de uiteindelijke sleutel. Licht je keuze toe.
ENIGMA
7.4
Verwerkingsboek
Debiteuren Naam
Adres
Woonpl
Postcode
Bankrek
Geb_dat
Klantnr
Doel E.vd Aarts J. Staats V. Koper D.
Brink 4 Stationspl 9 Veerlaan 51 Trans 73
Vleuten Utrecht Delfzijl Rotterdam
5466 WX 1648 ES 6584 TG 2856 UM
34546654 47398573 16450406 57839847
07-11-1955 18-04-1972 30-02-1960 05-08-1948
K475849 K769020 K243527 K810998
Klantnr
Aantal
Artikelnr
K769020 K769020 K810998 K810998 K810998 K095847
3 14 4 1 17 8
A374839 A968462 A968462 A550023 A445201 A344426
Bestellingen
Voorraad
b c d e
Artikel
Artikelnr
Prijs
Aantal
Accu Buitenspiegel Buitenspiegel Buitenspiegel V-snaar Lampenset
A374839 A445201 A968462 A146376 A664883 A550023
135,55 120,00 151,00 188,70 30,50 13,95
145 61 47 21 44 113
Wat is de sleutel in de tabel Bestellingen? En in de tabel voorraad? Welk(e) artikel(en) heeft D.Koper besteld? Vul de kolom Voorraad.Aantal in zoals die eruit ziet na levering van de bestelling van D.Koper. Waarom moet bij de kolom Aantal de tabelnaam Voorraad genoemd worden?
2 INFORMATIEMODELLERING VERWERKEN 16 Welke fasen worden er onderscheiden in de ontwikkeling van een informatiesysteem? 17 Bij het ontwikkelen van een informatiesysteem kan gebruik gemaakt worden van de methode FCO-IM. Deze methode gaat er vanuit dat alle relevante informatie in voorbeeldzinnen kan worden geformuleerd. Deze voorbeeldzinnen drukken een eenduidig feit uit dat in de te modelleren communicatie van belang is of van belang kan zijn. Welke van de volgende zinnen drukken één eenduidig feit uit? a b c d e
Alan Alan Alan Alan Alan
Turing Turing Turing Turing Turing
is een leerling. speelt gitaar en voetbalt. fietst naar school. woont op Industrieweg 85 Zutphen. heeft een 7 voor informatica.
Copyright © 2007 - 2011
ENIGMA-online
Hoofdstuk 7
7.5
Databases
18 Bekijk de onderstaande tabelstructuur: voornaam a b c
achternaam
adres
woonplaats
hobby
vak
cijfer
Deze tabelstructuur zorgt ervoor dat er redundantie optreedt. Leg dat uit. Redundantie kan leiden tot inconsistentie. Waarom mag er in een database geen inconsistentie optreden? Maak een nieuwe tabellenstructuur, zodat deze problemen vermeden worden.
TOEPASSEN 19 Alan heeft een grote verzameling cd‟s. Vrienden vragen regelmatig of zij een cd mogen lenen. Om geen cd‟s kwijt te raken, besluit hij een administratie op te zetten voor zijn collectie. In het systeem wil hij de titel van een cd, de naam van een artiest, het soort muziek en de prijs van een cd opnemen. Daarnaast moeten de naw-gegevens van diegene die de cd leent worden vastgelegd. Omdat ook wel eens gevraagd wordt of hij een bepaald liedje heeft, besluit Alan dat alle nummers die op een cd staan in het systeem moeten worden opgenomen. a b c
Welke gegevens wil Alan vastleggen in het systeem? Welke functies moet het systeem bezitten? Ontwerp een tabellenstructuur voor het cd-uitleensysteem.
3 STRUCTURED QUERY LANGUAGE (SQL) VERWERKEN 20 Om de tabellenstructuur van een database weer te geven, wordt vaak een strokendiagram gebruikt. Bekijk het strokendiagram van de tennisvereniging in je informatieboek. a Hoe wordt de sleutel van een tabel aangeduid in een strokendiagram? b Noteer de namen van de vier tabellen van de database van de tennisvereniging en geef aan wat de bijbehorende sleutels zijn. c De sleutel van de tabel scores bestaat uit twee kolommen. Waarom kan de kolom lidnr alléén niet de sleutel van die tabel zijn? 21 Tussen de verschillende tabellen in een database bestaan verbanden. a Hoe worden de verbanden tussen tabellen in een strokendiagram aangegeven? b Schrijf alle verbanden op die er in de database van de tennisvereniging zijn. 22 In het informatieboek lees je wat een vraagtaal is. a Leg in eigen woorden uit waarvoor je een vraagtaal gebruikt. b Noem een voorbeeld van een (veelgebruikte) vraagtaal.
ENIGMA
7.6
Verwerkingsboek
23 In het informatieboek staat de query:
SELECT FROM a b
voornaam, naam, telefoonnr leden;
Beschrijf in eigen woorden wat hier wordt bedoeld. Je ziet dat de SQL-woorden met hoofdletters worden getypt. Dat is niet noodzakelijk maar waarom zou het in het algemeen toch wel worden gedaan?
24 Je kunt een selectie van kolommen maken uit een bepaalde tabel. Je kunt echter ook alle kolommen selecteren. a Hoe kun je dat snel doen? b Met welke query selecteer je alle kolommen uit de tabel boetes? 25 In het informatieboek lees je dat je ook selecties kunt maken. a Welk commando gebruik je daarvoor? b Wat is de voorwaarde in de volgende query?
SELECT FROM WHERE
voornaam, naam, telefoonnr leden geslacht = ‘V’;
26 In het informatieboek staat ook de volgende query:
SELECT FROM WHERE a b c
lidnr, datum, bedrag boetes bedrag > 50;
Wat selecteert deze query? In de query bij vraag 25 staat V tussen aanhalingstekens. Bij deze query staat 50 niet tussen aanhalingstekens. Waarom is dat? Hoe wordt een datum genoteerd in Access?
27 In het informatieboek lees je hoe je selecties gesorteerd weer kunt geven. a Welk commando gebruik je daarvoor? b Hoe wordt de volgende selectie gesorteerd?
SELECT FROM WHERE ORDER BY
voornaam, naam, telefoonnr leden geslacht = ‘V’ naam;
Copyright © 2007 - 2011
ENIGMA-online
Hoofdstuk 7
Databases
7.7
28 Bekijk de volgende query:
SELECT FROM ORDER BY a b c d
bedrag, lidnr, datum boetes bedrag, datum;
In de laatste regel zie je „bedrag‟ en „datum‟ staan. Verklaar waarom. Wat wordt de query wanneer je eerst sorteert op datum en vervolgens op bedrag? Wordt de lijst oplopend of aflopend gesorteerd? Herschrijf de query, zodat de lijst „aflopend‟ gesorteerd wordt.
29 In het informatieboek wordt het commando SELECT DISTINCT toegelicht. a Leg in eigen woorden uit wat dit commando doet. b Geef een voorbeeld van een query waarin je gebruik maakt van dit commando. c Maakt het verschil als je in dit voorbeeld SELECT DISTINCT vervangt door SELECT?
30 Open in Access de database tennisvereniging.mdb als je werkt met Access 2000 of Access 2002-2003. Beschik je over Access 2007 gebruik dan het bestand tennisvereniging.accdb. Beide bestanden zijn te vinden op de Enigmasite. Nadat je op „tabellen‟ hebt geklikt, open je de tabel „leden‟. a Verander de straatnaam van Cindy van den Akker in Dorpstraat en klik daarna op een willekeurige plaats in de volgende rij. Maak vervolgens de verandering ongedaan. (Bewerken → Record opslaan ongedaan maken in Access 2000 of Access 2002-2003. In Access 2007 kies je gewoon voor ongedaan maken) b Verander nu het lidnummer van Twan Bakker in 85 en klik weer ergens in de volgende rij. Schrijf op wat er gebeurt. c Klik met de rechtermuisknop in de tabelheader op naam. Er verschijnt nu een snelmenu. Kies voor Oplopend sorteren. In Access 2007 kies je Sorteren van A naar Z. 31 Om een query te maken moet je eerst een aantal handelingen uitvoeren. Volg de instructies die horen bij de versie van Access waarmee je werkt: Access 2000 en Access 2002-2003 1. Klik onder Objecten op de knop Query’s in het venster tennisvereniging: Database 2. Kies voor Query maken in ontwerpweergave. 3. Kies Sluiten in het venster Tabel toevoegen. 4. Klik in de knoppenbalk links vooraan op het driehoekje met de punt naar beneden van de Beeldknop.
ENIGMA
7.8
Verwerkingsboek
5. Kies SQL uit het submenu.
1
2
Access 2003
Access 2007
1. Klik op het tabblad Maken en kies voor Queryontwerp. 2. Kies Sluiten in het venster Tabel weergeven. 3. Je krijgt nu een venster om een query in te typen:
3
In het venster staat bij de verschillende versies van Access al het eerste woord: SELECT. De puntkomma hoort er te staan als afsluiting van een opdracht. a
Maak nu de onderstaande query:
Wanneer je de query klaar hebt, kun je de query laten uitvoeren. In Access versie 2000 en Access versie 2002-2003 doe je dat door op de Beeld-knop te klikken en vervolgens voor Gegevensbladweergave te kiezen. In Access 2007 kies je onder Weergave voor Gegevensbladweergave. b c
Hoe is de nieuwe tabel gesorteerd? Keer terug naar het SQL-venster en vul de query aan met lidnr.
SELECT FROM
lidnr, voornaam, adres, woonplaats leden;
Copyright © 2007 - 2011
ENIGMA-online
Hoofdstuk 7
d
7.9
Databases
Voer ook deze query uit. Klopt jouw antwoord op vraag 31b?
32 Beantwoord door gebruik te maken van query‟s de volgende vragen. Schrijf de query ook op. a Maak een lijst van alle leden (voor- en achternaam) die in Gaanderen wonen. b Maak een lijst van leden die al meer dan 25 jaar lid zijn van de vereniging. c Maak een lijst van teams die in de 4e klasse spelen. d Hoeveel boetes van 100 euro zijn er door de tennisbond opgelegd? e Maak een lijst van alle gewonnen partijen. f Maak een lijst waarin alle leden staan die na 1 januari 1975 zijn geboren. g Maak een lijst van leden (lidnr) die meer wedstrijden voor hun team hebben gewonnen dan verloren. 33 Tot nu toe hebben we elke keer query‟s uitgevoerd op de database van de tennisvereniging. In het verwerkingsboek gaan we ook aandacht besteden aan een andere database, namelijk de cd-dvd database. De cd-dvd database bestaat uit 4 tabellen. In de tabel leners staan de gegevens van mensen die cd‟s of dvd‟s lenen. Denk daarbij aan voornaam, achternaam, adres, postcode, woonplaats en telefoonnummer. Elke lener heeft een uniek nummer, het zogenaamde adminnr. In de tabel uitlening wordt bijgehouden wie wat heeft geleend en wanneer iets teruggebracht is. Een omschrijving van alle cd‟s en/of dvd‟s is terug te vinden in de tabel cd_dvd. In deze tabel treffen we de namen van artiesten aan, de titels van de nummers, het jaar waarin de release heeft plaatsgevonden, het genre waartoe het nummer behoort en een type-omschrijving. Iedere cd en/of dvd wordt in deze tabel uniek geïdentificeerd aan de hand van een cdid. Tot slot is er dan nog de tabel nummers. Voor de weergave van de tabellenstructuur van de cd-dvd database gebruiken we net zoals bij de database van de tennisvereniging een strokendiagram:
leners adminnr
achternaam
voornaam
adres
postcode
woonplaats
nummers cdid
nummer
uitleendatum
datumterug
artiest
jaar
uitlening adminnr
cdid
cd_dvd cdid
titel
genre
type
telefoon
ENIGMA
7.10 a b c d
Verwerkingsboek
Noem de vier tabellen uit de cd-dvd database. Welke gegevens staan er in de tabel leners? Wat is de sleutel in de tabel uitlening? Waaraan zie je dat in het strokendiagram?
TOEPASSEN 34 Beantwoord, door gebruik te maken van query‟s, de volgende vragen. Schrijf de query‟s ook op. a Maak een lijst van leners die in Wehl wonen. b Maak een lijst van alle woonplaatsen waar leners wonen. Er mogen in deze lijst géén duplicaten voorkomen. c Maak een lijst van alle cd‟s van de groep Pink Floyd die in de database zijn opgenomen. 35 a Welke lener woont er in de Tulpenstraat 103 in Doetinchem? b Welke cd van Queen kwam in 1975 uit? c Geef een overzicht van alle nummers die in de database voorkomen. Sorteer deze nummers oplopend. d Maak een lijst van de namen van artiesten waarvan nummers onder het genre blues in de database zijn verwerkt. Gebruik voor de volgende vragen de database van de tennisvereniging: 36 a Maak een lijst van alle leden die in Doetinchem wonen. Sorteer oplopend op achternaam b Maak een lijst van de aantallen gewonnen partijen en sorteer oplopend op lidnummer en aflopend op gewonnen partij. c Maak een lijst van de NAW-gegevens van de leden gesorteerd op woonplaats, naam en adres. 37 a Op welke data kreeg de vereniging een boete opgelegd? b Maak een alfabetisch geordende lijst van de woonplaatsen waar de leden vandaan komen. c In welke klassen heeft de tennisvereniging vertegenwoordigingen? VERDIEPEN 38 In Access kun je ook de relaties tussen de verschillende tabellen zichtbaar maken. In de versies van Access 2000 en 2002-2003 doe je dat door in de werkbalk te klikken op de Relaties-knop.
Copyright © 2007 - 2011
ENIGMA-online
Hoofdstuk 7
Databases
7.11
Door te slepen kun je het venster er wat overzichtelijker uit laten zien. Zie daarvoor de volgende afbeelding.
a b
Hoe worden de sleutels van de verschillende tabellen aangegeven? Als je dit relatievenster vergelijkt met het strokendiagram in het informatieboek, wat valt je dan op? Sluit vervolgens het relatievenster.
4 SAMENGESTELDE VOORWAARDEN VERWERKEN 39 Om te weten te komen wie er op de Vordenseweg 7 woont, zou je de volgende query kunnen maken:
SELECT FROM WHERE a b
voornaam, naam leden adres = ‘Vordenseweg 7’;
Waarom voldoet deze query niet? Hoe zou je deze query moeten aanpassen om wel het gewenste resultaat te krijgen?
40 Behalve de AND-operator is er ook de OR-operator. a Leg in eigen woorden uit waar je deze operator voor kunt gebruiken? b Bekijk de volgende query:
SELECT FROM
voornaam, naam leden
ENIGMA
7.12 WHERE
Verwerkingsboek
enkel =’E’ OR dubbel = ‘E’;
Krijg je in deze lijst ook de leden die in het enkel- en het dubbelspel in de klasse E zijn ingedeeld? 41 In sommige gevallen kun je in plaats van de OR-operator de LIKE-operator gebruiken. Kijk bijvoorbeeld naar de volgende query:
SELECT FROM WHERE a b
voornaam, naam leden enkel =’C1’ OR enkel = ‘C2’;
Herschrijf deze query door gebruik te maken van de LIKE-operator. Wat is in de LIKE-operator het verschil tussen een * en een ? ?
42 Bekijk de volgende query:
SELECT FROM WHERE a b
voornaam, naam leden NOT woonplaats =’Doetinchem’;
Wat selecteert deze query? Er is een andere manier om hetzelfde resultaat te krijgen. Dan moet je gebruik maken van één van de relationele operatoren van SQL. Herschrijf de bovenstaande query op die manier
43 Met welke operator selecteer je lege velden? 44 In het informatieboek zie je een voorbeeld van een query met samengestelde voorwaarden:
SELECT FROM WHERE
voornaam, naam leden enkel =’C2’ OR enkel = ‘C1’ OR enkel = ‘B2’ OR enkel = ‘B1’;
Dat is een omslachtige manier. a Met welke operator kun je deze query vereenvoudigen? b Hoe ziet de query eruit die alle leden selecteert die in Doetinchem, Wehl of Braamt wonen? 45 Bekijk de volgende query uit het informatieboek: Copyright © 2007 - 2011
ENIGMA-online
Hoofdstuk 7
SELECT FROM WHERE
a b c
Databases
7.13
voornaam, naam, geslacht, enkel leden enkel =’D1’ OR enkel = ‘D2’ AND geslacht = ‘V’;
Worden in deze query ook mannen geselecteerd? Geef daarvan een voorbeeld. Herschrijf deze query zodanig dat alleen vrouwen die in de klasse D1 of D2 spelen geselecteerd worden. Hoe ziet de query eruit die alle mannen uit Wehl en Braamt selecteert?
TOEPASSEN 46 Gebruik weer het bestand tennisvereniging.mdb of tennisvereniging.accdb. Schrijf altijd ook de query‟s op bij het maken van de opdrachten. a Binnenkort moet er een brief verstuurd worden aan alle mannelijke leden die woonachtig zijn in Doetinchem. Maak een lijst waarin staan: voor- en achternaam, adres en woonplaats. b Welke leden hebben meer dan 6 keer gewonnen en minder dan 4 keer verloren? c Maak een lijst van alle leden uit Doetinchem en Braamt. Lukt dat met de operator AND? Waarom wel of waarom niet? d Over 14 dagen wordt er een toernooi georganiseerd voor alle E-spelers. De toernooicommissie heeft een lijst nodig van alle leden die mee mogen doen aan het toernooi. Men wil op de lijst de voornaam, de achternaam, niveau enkelspel en niveau dubbelspel zien. Een lid mag meedoen aan het toernooi als zijn/haar niveau enkelspel E is, zijn/haar niveau dubbelspel E is en natuurlijk ook als beide niveaus E zijn. Schrijf een query die deze lijst genereert. 47 a Welke vrouwelijke leden wonen aan de Sikkeldreef in Doetinchem? (Let op: in Keijenborg is ook een straat die zo heet) b Het bestuur wil een commissie vormen die een jeugdtoernooi gaat organiseren. Daartoe wil men een lijst samenstellen van leden die na 1984 zijn geboren en tot de categorie Enkel-E-spelers behoren. Stel de benodigde query op. c Maak een lijst van de namen van spelers en de inschrijfdatum, die in de periode 1 januari 1980 tot en met 31 december 1985 lid zijn geworden. d Een aantal leden heeft een geheim telefoonnummer. Maak een lijst van de mannelijke leden die geen geheim telefoonnummer hebben en in Doetinchem wonen. 48 a Maak een lijst van de lidmaatschapsnummers van de spelers, die in een van de herenteams uitkomen. b De secretaris wil de gegevens opvragen van iemand waarvan hij niet zeker weet of hij Mark Jansen of Mark Janssen heet. Schrijf de juiste query. 49 a De penningmeester wil van iemand alle gegevens zien. Hij weet echter niet meer dan dat er in de straatnaam van die persoon het woord havik voorkomt. Maak de query.
ENIGMA
7.14 b c d
Verwerkingsboek
Susanne Blok, die woont aan de Boekweitdreef in Doetinchem is ziek en de secretaris wil bij haar een bloemetje laten bezorgen door iemand uit haar straat. Stel de query op. Zoek de gegevens van iemand die de achternaam Smeitink heeft, maar niet in Doetinchem woont. Welke leden hebben een boete van 50 euro of hoger?
50 Open in Access de database cd-dvd. Gebruik deze database voor de volgende opdrachten. a Welke leners wonen in Doetinchem en hebben een postcode die begint met 7006? b Geef het adminnr van de lener die op 13 november 2003 een cd heeft geleend en deze cd op 28 december 2003 weer heeft teruggebracht. c Geef een overzicht van alle leners waarvan de achternaam begint met Ri. d Geef de titel van de door Bruce Springsteen uitgebrachte DVD die tot de categorie Pop wordt gerekend. VERDIEPEN 51 a Maak een lijst van de lidmaatschapsnummers van de spelers,die meer wedstrijden gewonnen dan verloren hebben. Sorteer op lidnummer. b Maak een lijst van alle leden die een lidnummer hebben dat ligt tussen de 50 en de 150. 52 a Maak een lijst met de lidmaatschapsnummers van de spelers, die uitkomen in de eerste twee heren, dames of mixed teams. b Maak een lijst met alle gegevens van de leden uit Didam, Gaanderen, Zelhem en Zevenaar. Sorteer op plaatsnaam. 53 a Maak een lijst van de D-spelers(enkel), die uit Braamt en Zevenaar komen. b Maak een lijst van de in Keijenborg en Braamt woonachtige leden die zich in de jaren 1974 en 1975 hebben ingeschreven. c Maak een lijst van leden die in de maand oktober jarig zijn. (Tip: laat in deze query de hekjes weg en beschouw de datum als een string). 54 Bekijk de onderstaande query
SELECT FROM WHERE ORDER BY a
b
voornaam, naam,inschrijfdatum leden inschrijfdatum >= #1-1-80# AND inschrijfdatum < #1-1-85# inschrijfdatum, naam;
Wat is het resultaat van deze query? SQL kent ook de BETWEEN-operator. Deze operator geeft aan dat een waarde van een expressie tussen twee grenzen ligt of precies een grenswaarde aanneemt. Bijvoorbeeld: bedrag BETWEEN 25 AND 50. Herschrijf de bovenstaande query met de BETWEEN-operator.
Copyright © 2007 - 2011
ENIGMA-online
Hoofdstuk 7
Databases
7.15
5 TABELLEN COMBINEREN VERWERKEN 55 Bekijk de volgende query:
SELECT FROM a b
voornaam, naam, bedrag leden, boetes;
Leg in eigen woorden uit waarom de tabel die deze query oplevert fout is. Herschrijf deze query zodat deze de juiste lijst oplevert.
56 Deze paragraaf gaat over het combineren van tabellen in een query. Dezelfde kolomnaam kan meerdere keren voorkomen. Bekijk het volgende voorbeeld:
SELECT FROM WHERE
lidnr, voornaam, naam, bedrag leden, boetes lidnr = lidnr;
Pas deze query zo aan, zodat er geen onduidelijkheid meer bestaat over de kolomnamen. 57 In het informatieboek staat de volgende query:
SELECT FROM WHERE AND
voornaam, naam, adres, woonplaats leden, boetes leden.lidnr = boetes.lidnr bedrag > 50;
Wat selecteert deze query? TOEPASSEN 58 Gebruik bij de volgende vragen het bestand tennisvereniging.mdb of tennisvereniging.accdb a De penningmeester van de tennisclub wil zijn financiële administratie bijwerken en heeft daarvoor onder andere een lijst nodig van de leden die boetes hebben betaald. In die lijst moeten de voor- en achternaam vermeld staan met de bedragen die zijn betaald. Sorteer op achternaam. b De tennisbond vraagt de vereniging een lijst op te sturen van alle teams met namen (voor- en achternaam) van de aanvoerders. Schrijf een query die deze lijst genereert. c Maak een lijst van alle leden met de aantallen gewonnen en verloren partijen. De lijst moet bevatten: voornaam, achternaam, woonplaats, gewonnen en verloren. d Bekijk de volgende query en schrijf in natuurlijke taal de betekenis op.
SELECT FROM WHERE
voornaam, naam leden, boetes leden.lidnr = boetes.lidnr;
ENIGMA
7.16
Verwerkingsboek
59 a Maak een lijst met de namen van de spelers en het team waarin ze spelen. Sorteer deze lijst op team. b De wedstrijdsecretaris wil een overzicht van de klassen waarin de verschillende teams spelen. In dat overzicht moeten ook de namen van de aanvoerders vermeld staan. Schrijf de query. 60 a Maak een lijst met de namen, het team, het aantal gewonnen en verloren wedstrijden van de spelers die in een mix-team spelen. b Maak een lijst van de leden die evenveel wedstrijden gewonnen als verloren hebben. In de lijst moeten worden vermeld: voor- en achternaam, teamcode en de gewonnen en verloren partijen.
VERDIEPEN 61 Gebruik bij de volgende opgave de cd-dvd database a Noem de voor- en achternamen van de leners die ooit de cd met cdid 470 hebben geleend. b Noem de nummers die op de cd met cdid 6 staan. 62 Gebruik bij de volgende opgaven de database van de tennisvereniging a Maak een lijst van de namen en het boetebedrag van de spelers die uitkomen voor het eerste herenteam. b Maak een lijst met de namen van de leden die een boetebedrag van 40 euro of meer hebben betaald. 63 In de onderstaande query moet je bij lidnr iedere keer aangeven welke tabel bedoeld wordt. Dat betekent veel typwerk.
SELECT FROM WHERE
leden.lidnr, voornaam, naam, bedrag leden, boetes leden.lidnr = boetes.lidnr;
In plaats van iedere keer de tabelnaam voluit te typen, kun je ook een zogenaamde alias gebruiken. Dezelfde query komt er dan zo uit te zien:
SELECT FROM WHERE a b c
L.lidnr, voornaam, naam, bedrag leden L, boetes B L.lidnr = B.lidnr;
Wat is een alias? Maak een lijst met de namen, het team, het aantal gewonnen en verloren wedstrijden van de spelers die in een herenteam spelen. Maak een lijst van de leden die meer wedstrijden verloren dan gewonnen hebben. In de lijst moeten worden vermeld: voor- en achternaam, teamcode en de gewonnen en verloren partijen.
Copyright © 2007 - 2011
ENIGMA-online
Hoofdstuk 7
Databases
7.17
6 FUNCTIES VERWERKEN 64 In het informatieboek lees je dat de functie SUM de som van een aantal getallen in een kolom berekent. a Wat is het resultaat van de volgende query?
SELECT FROM b
SUM(bedrag) boetes;
Wat is er mis met de volgende query?
SELECT FROM
SUM(teamcode) teams;
65 SQL kent nog meer functies. a Leg uit wat je met de volgende query selecteert:
SELECT FROM WHERE b
COUNT(*) leden NOT woonplaats = ‘Doetinchem’;
Hoe geef je het resultaat dat deze query genereert de naam andere_leden? Schrijf de hele query op.
TOEPASSEN 66 Gebruik weer Access en de database van de tennisvereniging. a Wat is het totale bedrag aan betaalde boetes? b Wie van de leden (voor- en achternaam) heeft de hoogste boete betaald en hoe hoog was dat bedrag? Schrijf ook de query op die je hebt gebruikt om het antwoord te vinden. c Wat is de hoogste boete die betaald moest worden? d Hoeveel wedstrijden zijn er in totaal gewonnen? e Hoeveel teams heeft de tennisvereniging? 67 Schrijf bij het beantwoorden van de volgende vragen ook de query op die je hebt gebruikt. a Wat is de geboortedatum van het jongste lid van de tennisvereniging? b Hoeveel vrouwen zijn voor het enkelspel ingedeeld op het niveau E? c Hoe groot is het gemiddelde bedrag aan opgelegde boetes? d Hoeveel boetes heeft Bram Visser gehad? e Hoeveel wedstrijden heeft het Dames-2 team gewonnen?
ENIGMA
7.18
Verwerkingsboek
68 Open de cd-dvd database a Hoeveel cd‟s van Genesis zijn in de tabel cd_dvd opgenomen? b Uit welk jaar dateert de oudste cd die in de database terug te vinden is?
VERDIEPEN 69 Gebruik de database van de tennisvereniging om de volgende vragen te beantwoorden. a Wat is de totale boete die betaald moet worden voor de spelers die in het eerste herenteam spelen? b Hoeveel mannen hebben een boete gehad? Geef aan de resultaattabel de naam „Boetes_mannen‟. c Welke leden (voor- en achternaam) spelen niet in de 4e klasse? d Hoeveel meer partijen zijn er gewonnen dan verloren? e Maak één lijst met de kolommen Gemiddeld_gewonnen en Gemiddeld_verloren. 70 Stel dat je de verschillende woonplaatsen zou willen tellen. Wanneer je dat in SQL zou willen doen dan zou dat met de DISTINCT-component kunnen.
SELECT FROM
COUNT DISTINCT(woonplaats) leden;
Let op!! Deze query werkt wel in standard SQL, maar niet in de SQL-versie van Access. Ga dit na. 71 In een query kun je ook berekeningen laten uitvoeren met behulp van rekenkundige operatoren +, -, *, /. Een voorbeeld vind je in de onderstaande query:
SELECT FROM WHERE ORDER BY a
b
leden.lidnr, voornaam, naam, gewonnen * 2 AS Punten leden, scores leden.lidnr = scores.lidnr gewonnen * 2 DESC, naam;
De ledenvergadering heeft besloten dat de leden die een boete krijgen voortaan de helft van de boete zelf moeten betalen. Maak een lijst van de leden waarin het lidnr, de naw-gegevens, de datum van de boete en het bedrag dat ze moeten betalen zijn opgenomen. Sorteer de lijst op teamcode. Wat is het verschil tussen het hoogste en het kleinste boetebedrag?
72 Gebruik de cd-dvd database om de volgende vraag te beantwoorden. a b
Hoeveel leners hebben ooit een cd of dvd geleend. Geef aan de resultaattabel de naam „ooit_geleend‟. Kunnen in deze resultaattabel dezelfde leners meerdere keren voorkomen? Licht je antwoord toe.
Copyright © 2007 - 2011
ENIGMA-online
Hoofdstuk 7
Databases
7.19
7 GROEPEREN VERWERKEN 73 Je leest in het informatieboek hoe je rijen kunt groeperen. a Welke commando‟s gebruik je daarvoor? b Bekijk de volgende query‟s:
SELECT FROM WHERE
count(*) AS aantal leden geslacht = ‘V’;
SELECT FROM WHERE
count(*) AS aantal leden geslacht = ‘M’;
Herschrijf deze query‟s tot één query door de tabel leden te groeperen op basis van geslacht. 74 Bekijk de volgende query uit het informatieboek:
SELECT FROM GROUP BY
woonplaats, geslacht, count(*) AS aantal_leden leden woonplaats, geslacht;
Leg in eigen woorden uit wat deze query selecteert en hoe de lijst gepresenteerd wordt. 75 Bekijk de volgende query uit het informatieboek:
SELECT FROM WHERE GROUP BY
woonplaats, count(*) AS aantal_leden leden enkel LIKE ‘D?’ woonplaats;
Leg in eigen woorden uit wat deze query doet en hoe de lijst gepresenteerd wordt. 76 Je kunt met de WHERE-component een voorwaarde opleggen aan de rijen die je groepeert. Bekijk bijvoorbeeld de volgende query:
SELECT FROM GROUP BY
geslacht, count(*) AS aantal leden geslacht;
Herschrijf deze query zodanig dat alleen de leden met een niveau C1 of C2 worden geselecteerd
ENIGMA
7.20
Verwerkingsboek
77 In het informatieboek lees je dat je ook de HAVING-component kunt gebruiken. a Leg uit wat de HAVING-component doet. b Wat is het verschil met de WHERE-component? c Wat selecteert de volgende query:
SELECT FROM GROUP BY HAVING
teamcode scores teamcode SUM(gewonnen) > 20;
TOEPASSEN 78 Gebruik voor de volgende vragen weer de database van de tennisvereniging. Schrijf ook de query‟s op die je hebt gebruikt. a Geef per inschrijfdatum het aantal aanmeldingen. b Geef per team het totale boetebedrag. c Geef per woonplaats het aantal verloren wedstrijden. d Maak een lijst met de namen van de leden gegroepeerd op woonplaats. e In welke woonplaats wonen meer dan 4 spelers? f Geef een overzicht van de teams die meer partijen gewonnen dan verloren hebben. g Geef per team het aantal leden dat uit Doetinchem komt. 79 Gebruik voor deze opgave de cd-dvd database. a b
Kijk nog eens naar vraag 69 a. Herschrijf deze query zodanig dat er geen dubbele leners meer voorkomen. Geef een overzicht van de adminnr‟s van leners die meer dan 3 keer iets hebben geleend.
VERDIEPEN 80 Van alle spelers wordt het niveau geregistreerd. Ieder lid heeft twee niveaus: een als enkelspeler, een als dubbelspeler. a Geef een overzicht van alle combinaties binnen de vereniging en het aantal spelers dat die combinatie heeft. b In welke klassen spelen er meer dan 3 spelers die als enkelspeler niveau B2, C1, C2 of D1 hebben? 81 Geef een lijst van de teams met daarbij de naam van de aanvoerder, de klasse en het aantal gewonnen en verloren wedstrijden per team.
Copyright © 2007 - 2011
ENIGMA-online
Hoofdstuk 7
7.21
Databases
8 SUBQUERY: QUERY IN EEN QUERY VERWERKEN 82 In het informatieboek lees je wat een subquery is. a Leg in eigen woorden uit wat een subquery is. b Noem een belangrijk verschil tussen een query en een subquery. c Geef een voorbeeld van een subquery. d Leg uit wat je met een subquery selecteert. 83 Bekijk de volgende query uit het informatieboek:
SELECT FROM WHERE
a b c
voornaam, leden lidnr IN (SELECT FROM WHERE
naam lidnr scores gewonnen = (SELECT MAX(gewonnen) FROM scores));
Het is lastig deze query in één keer te doorgronden. Ontrafel deze query‟s in subquery‟s en beschrijf van iedere query afzonderlijk wat er gebeurt. Waarom wordt in de derde regel de IN-operator gebruikt en niet de =-operator? Beschrijf wat je uiteindelijk met deze query selecteert.
TOEPASSEN 84 Gebruik voor de volgende vragen weer de database van de tennisvereniging. a Geef het lidmaatschapsnummer van het lid dat de hoogste boete heeft gekregen. b Geef de voor- en achternaam en de inschrijfdatum van degene die het langst lid is. Gebruik hiervoor een subquery. c Geef het lidmaatschapsnummer van de leden die een hogere boete dan het gemiddelde boetebedrag hebben gekregen. d Welke leden waren al voor Thomas Barnholtz lid van de vereniging? 85 Open de cd-dvd database a Geef de voor- en achternaam van de lener die meer dan 3 keer iets heeft geleend. b Geef het adminnr en de voor- en achternaam van leners die iets hebben geleend, maar dat nog niet hebben teruggebracht.
ENIGMA
7.22
Verwerkingsboek
VERDIEPEN 86 Het wedstrijdsecretariaat wil weten welke speler het beste resultaat heeft behaald. Het criterium daarvoor is het verschil tussen het aantal gewonnen en verloren wedstrijden. Maar hiervoor een query. 87 Hieronder staan twee query‟s:
SELECT FROM WHERE
SELECT FROM WHERE
a b c
voornaam, leden lidnr IN (SELECT FROM WHERE
naam lidnr scores gewonnen = (SELECT MAX(gewonnen) FROM scores));
voornaam, naam leden, scores leden.lidnr = scores.lidnr AND gewonnen = (SELECT MAX(gewonnen) FROM scores);
Wat is het resultaat van de eerste query? Wat is het resultaat van de tweede query? In de eerste query staat lidnr IN en in de tweede query leden.lidnr Leg uit waarom.
88 De ALL- en ANY-operatoren Voor de volgende vragen gaan we uit van de database van de tennisvereniging. In opgave 87 heb je 2 query‟s bekeken die een oplossing geven op dezelfde vraag. Met behulp van de operator ALL kan deze query ook gemaakt worden.
SELECT FROM WHERE
a
voornaam, leden lidnr IN (SELECT FROM WHERE
naam lidnr scores gewonnen > = ALL (SELECT gewonnen FROM scores));
Leg aan de hand van het bovenstaand voorbeeld uit wat de ALL–operator doet. Je zou ook kunnen vragen naar een lijst van namen van de spelers die niet de meeste wedstrijden gewonnen hebben. Dit kan met behulp van de operator ANY
SELECT FROM
voornaam, naam leden
Copyright © 2007 - 2011
ENIGMA-online
Hoofdstuk 7
WHERE
b
7.23
Databases
lidnr IN (SELECT FROM WHERE
lidnr scores gewonnen < ANY (SELECT gewonnen FROM scores));
Leg aan de hand van bovenstaand voorbeeld uit wat de ANY-operator doet.
Maak de volgende opgaven met behulp van de ALL- of ANY-operator c d e f
Geef de naam en de geboortedatum van het oudste lid /oudste leden. Geef de namen van de leden die niet de hoogste boete hebben opgelegd gekregen. Geef de namen van de leden voor wie tenminste één boete is betaald. Welk team heeft de meeste wedstrijden gewonnen?
89 De EXISTS-operator Stel de wedstrijdsecretaris wil een lijst van leden die nog nooit een boete hebben gehad. Deze vraag is wat lastiger te beantwoorden. Daarvoor moeten we de EXISTS–operator gebruiken:
SELECT FROM WHERE
a
voornaam, naam leden NOT EXISTS (SELECT * FROM boetes WHERE leden.lidnr = boetes.lidnr);
Leg aan de hand van het bovenstaande voorbeeld uit wat de EXISTS–operator doet.
Maak de volgende opgaven met behulp van de EXISTS–operator. b c
Geef de namen van de leden voor wie tenminste één boete is betaald. Geef de namen van de competitiespelers die geen aanvoerder zijn.
9 EEN DATABASE MAKEN MET SQL VERWERKEN 90 a Dillon Stolle krijgt een nieuw telefoonnummer. Zijn oude nummer (0314) 544451 wordt (0314) 538700. Stel de query op waarmee je deze wijziging kunt doorvoeren. b Formuleer de query waarmee de tabel boetes geleegd kan worden. 91 In het informatieboek wordt uitgelegd hoe je met behulp van een query de tabel leden in een lege database van de tennisvereniging zou kunnen maken.
ENIGMA
7.24
Verwerkingsboek
Stel de query op waarmee de tabel teams kan worden gecreëerd. (Tip: bekijk in Access in de gevulde database de tabel teams in de ontwerpweergave om te zien welke gegevenstypes bij de verschillende kolommen zijn gebruikt)
TOEPASSEN 92 Het Rembrandtkwartier in Alkmaar heeft een zeer actieve buurtvereniging. Elk jaar organiseert deze vereniging een viswedstrijd voor de kinderen van de leden in de leeftijd van 10 tot 18 jaar. Een maand voordat de wedstrijd plaatsvindt, krijgen al deze kinderen een brief met informatie over de wedstrijd en een antwoordstrookje waarmee ze zich kunnen opgeven. Voor het versturen van de brieven wordt gebruik gemaakt van de gegevens die zijn opgenomen in de database van de buurtvereniging. De naw-gegevens van de leden en de gegevens van de kinderen van de leden zijn in twee aparte tabellen opgenomen. a Ontwerp het strokendiagram voor deze twee tabellen. b Maak met behulp van SQL de database voor de buurtvereniging.
Copyright © 2007 - 2011
ENIGMA-online
