atica inform databases & SQL

informatica databases & SQL

Op dit lesmateriaal is een Creative Commons licentie van toepassing. 2010

Remie Woudt [email protected]

2013 François Vonk (database onderwerpen geïsoleerd, XAMP vervangen door USBWebserver, minor edits, JOIN uitgebreid en sub-query's toegevoegd) 2014 François Vonk (exporteren database uitgelegd, extra opgaves voor maken van databases in USB Webserver, normaliseren sectie uitgebreid met meer vragen) 2016 is)

François Vonk (minder essentiële opgaven uit hoofdstuk 1 verwijderd, zodat dit hoofdstuk compacter

Inhoud 1databases........................................................................................1 1.1bij de dierenarts...........................................................................................2 1.2MySQL.........................................................................................................2 1.3database definiëren & tabel maken.............................................................3 1.4tabel vullen...................................................................................................5 1.5Een slimme tabel?.......................................................................................6 1.6dubbele gegevens voorkomen....................................................................7 1.7relationele database....................................................................................7 1.8sleutelvelden................................................................................................8 1.9sleutels gebruiken......................................................................................10 1.10soorten relaties........................................................................................11 1.11het strokendiagram..................................................................................12 1.12databases exporteren..............................................................................13 1.13databases normaliseren..........................................................................15

2data normalisatie (vwo).................................................................17 2.1eerste normaalvorm...................................................................................17 2.2tweede normaalvorm.................................................................................19 2.3derde normaalvorm...................................................................................20 2.4toepassen..................................................................................................22

3SQL...............................................................................................23 3.1Data Definition Language..........................................................................23 3.2Data Manipulation Language....................................................................24 3.3SELECT.....................................................................................................25 3.4AND en OR................................................................................................27 3.5IN en BETWEEN.......................................................................................28 3.6de aggregaat functies................................................................................30 3.7wiskundige berekeningen..........................................................................31 3.8ORDER BY................................................................................................32 3.9GROUP BY................................................................................................33 3.10HAVING...................................................................................................34 3.11tabellen combineren met JOIN................................................................35 3.12sub-query's (vwo)....................................................................................37

Deze methode gaat over databases en SQL. Let op, de vwo stof over data normalisatie en sub-query's zijn geen schoolexamenstof voor havo! In deze module kom je opgaves, aangegeven in een blauwe achtergrond, tegen die je moet maken om de lesstof te verwerken. De antwoorden kunnen in de les besproken worden.

1 databases Volgens Wikipedia is een database (gegevensbank of databank) een digitaal opgeslagen archief, ingericht met het oog op flexibele raadpleging en gebruik. Als je op internet gaat kijken zul je merken dat er nogal wat definities zijn van het woord database. Vaak wordt de database als systeem gezien terwijl dat helemaal niet correct is omdat het systeem meer bevat dan de database. Wij zullen het volgende aanhouden: • De database is de data en diens structuur (datamodel). • Het Database Management System (DBMS) is de software die ons in staat stelt om de structuur van een database te definiëren, de database te vullen met data en informatie op te vragen uit de database. • Het database systeem is de combinatie van de database en het DBMS. In dit hoofdstuk zullen we vooral kijken naar het datamodel en we zullen daarbij gebruik maken van een DBMS. Het datamodel en het DBMS hebben met elkaar te maken. Wanneer we de gegevens opslaan in een tabelstructuur zullen we ook een database management systeem moeten hebben dat die manier van opslaan ondersteunt. Uit de vorige zin blijkt al dat er verschillende manieren zijn om gegevens op te slaan. De meest gebruikte manier is in de vorm van een stelsel van tabellen. In zo’n tabel zitten velden die een relatie met elkaar hebben. Dat klinkt nog ingewikkeld maar verderop gaan we er dieper op in en dan zal het gauw duidelijk worden wat er mee bedoeld wordt. Een dergelijke manier van dataopslag wordt het relationele model genoemd en het management systeem is dan een RDBMS (Relational Database Management System). Een andere manier van gegevens opslaan is het ODBMS (Object Oriented Database Management System). Deze is vooral ontwikkeld om een betere communicatie te krijgen tussen object georiënteerde programmeertalen en het DBMS. In deze module zullen we ons alleen bezighouden met relationele modellen. Om het inrichten (structuur aanbrengen) en gebruiken van een relationeel database systeem te begrijpen beginnen we met enkele voorbeelden.

Hoofdstuk 1databases

1

datamodel database management system DBMS

relationele model RDBMS ODBMS

1.1 bij de dierenarts Stel je hebt een hond en je moet voor het eerst met hem/haar naar de dierenarts. De dierenarts heeft dan gegevens nodig over jou en je hond. Je kunt daarbij denken aan: • • • • • • • • • • • •

je voorletters je eventueele tussenvoegsels je achternaam je NAW (naam, adres, woonplaats) gegevens je telefoonnummer je verzekeringsgegevens je rekeningnummer de naam van je hond het geslacht van je hond het ras van je hond de geboortedatum van je hond ...

Oftewel een hele lijst met gegevens die de dierenarts nodig heeft om je hond te kunnen helpen en later ook een rekening te kunnen sturen.

1.2 MySQL MySQL

Om de gegevens van de dierenarts op te slaan kan hij/zij gebruik maken van een database systeem. Een veel gebruikt type database systeem MySQL. Dit systeem bestaat uit een relationele DBMS (Database Management System) en database. Om hiermee te werken, ga je USB Webserver gebruiker, omdat deze een MySQL database systeem bevat. Je kunt een goed werkende versie vinden in de ELO. Om goed te kunnen werken is het noodzakelijk dat je zelf een USB stick hebt en die elke les meeneemt. Hoe je USB Webserver op je USB stick krijgt en hoe het werkt kun je vinden in het document USB Webserver installatie & gebruik dat in de ELO staat. Nadat je USB Webserver hebt geïnstalleerd, ga je een database structuur (datamodel) definiëren binnen MySQL. Een datamodel is niets anders dan een aantal tabellen, waarbinnen je per tabel aan gaat geven wat je op wilt slaan. Om gegevens in een tabel op te slaan, moeten we vooraf weten met welke soort gegevens we te maken hebben. Gegevens kunnen bijvoorbeeld alfanumeriek (tekst & getallen), numeriek (getallen) of Boolean (waar/onwaar) zijn. In de volgende opdracht ga je eerst de structuur vastleggen van de gegevens je bij de dierenarts hebt moeten opgeven. Zorg altijd, dat USB Webserver runt op je computer via je USB stick.

2

1.2MySQL

1.3 database definiëren & tabel maken Opdracht 1-1 Voer de instructies die in deze paragraaf staan stap voor stap uit in de PHPMyAdmin omgeving van USB Webserver. Lees goed en probeer te snappen wat je aan het doen bent. Van alleen op knoppen drukken leer je bijzonder weinig! De database structuur gaan we aanmaken via de PHPMyAdmin knop. Deze knop geeft je toegang tot het MySQL RDBMS. Hierdoor kunnen we structuren definiëren, data invoeren en informatie opvragen. Dus als je USB Webserver hebt opgestart, open de applicatie console en druk op de PHPMyAdmin knop. Log in met de standaard gebruikersnaam wachtwoord combo. Je krijgt dan het scherm te zien dat lijkt op afbeelding 1. De database die we gaan maken noemen we dierenarts. Typ in het veld waar boven staat: Nieuwe database aanmaken: dierenarts en klik op aanmaken. In het volgende scherm (zie afbeelding 2) krijg je de melding dat de database inderdaad is aangemaakt. Nu kun je binnen die database tabellen aanmaken.

Afbeelding 1: Het startscherm van PHPMyAdmin

Afbeelding 2: De database is aangemaakt

Hoofdstuk 1databases

3

Je hebt nu een database aangemaakt. Een database bestaat uit 1 of meerdere tabellen. Er moet altijd tenminste 1 tabel in een database zitten. Als het goed is, weet je wat een tabel is. Je hebt hopelijk wel eens een tabel in MS Word gebruikt. Zoals je weet bestaat een tabel uit kolommen (verticaal) en rijen (horizontaal). Bij een tabel van een database is dat niet anders. De termen die echter voor de kolommen en rijen gebruikt worden zijn anders. field/veld

Een kolom van een tabel in een database noemen we een field (veld). Via de velden leggen we de structuur van de tabel vast. De namen van de velden geven dus aan wat voor soort informatie er in de kolom van de tabel opgeslagen gaat worden. Je gaat nu de structuur van je database vastleggen. Voer als tabelnaam: klanten in, typ bij het aantal velden het getal 12 en druk op Start. (zie afbeelding 3).

Afbeelding 3: Een tabel invoeren Je krijgt nu een invulscherm met 12 in te vullen regels. Vul op de eerste regel bij Veld in: naam. Selecteer bij Type: varchar en voer bij de lengte in: 25 (zie afbeelding 4). Laat de rest van de velden van die regel leeg. Op deze manier reserveer je een opslagruimte van maximaal 25 karakters in de tabel klanten met de naam naam. Zo gaan we ook de andere velden vullen.

Afbeelding 4: Het invulscherm om een tabel aan te maken

4

1.3database definiëren & tabel maken

Maak na naam ook de volgende velden aan: voorletters_en_voorvoegsel adres postcode woonplaats telefoon naam_dier soort_dier ras geboortedatum gewicht kleur

varchar varchar varchar varchar varchar varchar varchar varchar date decimal varchar

15 25 7 25 15 25 15 15 7,3 15

Alle overige invulvelden kun je leeg laten. Klik tenslotte op Opslaan en de tabel wordt aangemaakt. Je ziet nu de tabelstructuur zoals je die net hebt aangemaakt. Bovenin zie je de volgende regel (afhankelijk van welke versie van USB Webserver je gebruikt):

tabelstructuur

CREATE TABLE `dierenarts`.`klanten` ( `naam` VARCHAR( 25 ) NOT NULL , `voorletters_en_voorvoegsel` VARCHAR( 15 ) NOT NULL , `adres` VARCHAR( 25 ) NOT NULL , `postcode` VARCHAR( 7 ) NOT NULL , `woonplaats` VARCHAR( 25 ) NOT NULL , `telefoon` VARCHAR( 15 ) NOT NULL , `naam_dier` VARCHAR( 25 ) NOT NULL , `soort_dier` VARCHAR( 15 ) NOT NULL , `ras` VARCHAR( 15 ) NOT NULL , `geboortedatum` DATE NOT NULL , `gewicht` DECIMAL( 7, 3 ) NOT NULL , `kleur` VARCHAR( 15 ) NOT NULL ) ENGINE = MYISAM ; Dit is de eigenlijke SQL opdracht waarmee PHPMyAdmin de tabel heeft aangemaakt en wij hadden dat ook zo kunnen doen. Maar de manier waarop wij dat nu gedaan hebben is wel een stukje handiger. SQL opdrachten komen in Hoofdstuk 3 van deze module aan de orde.

1.4 tabel vullen Het is hopelijk duidelijk, dat een tabel is bedoeld om gegevens in op te slaan en dus is het tijd om de net aangemaakte tabel te vullen met onze gegevens. Bij elkaar horende gegevens komen in dezelfde rij in de tabel te staan. Dus al jouw gegevens staan in 1 rij. Zo'n rij noemen we een record. Gegevens van de andere klanten van de dierenarts staan ook in de tabel, maar hebben natuurlijk een eigen record.

Opdracht 1-2 •

Klik op het tabblad

•

Vul de volgende waarden in en klik op Start:

Hoofdstuk 1databases

5

record

Afbeelding 5: Gegevens in een tabel invoegen Hoewel we hierna een foutmelding krijgen omdat we de geboortedatum en het gewicht niet hebben ingevoerd, worden de gegevens wel aan de tabel klanten toegevoegd. Klik maar bovenin op Tabel: klanten. Je hebt nu het eerste record aan de tabel toegevoegd.

1.5 Een slimme tabel? In de vorige opdrachten heb je een tabel gedefinieerd en er een record aan toegevoegd. Maar voordat we verder gaan en de gehele dierenartsenpraktijk in de tabel gaan onderbrengen, moeten we ons eerst afvragen of de structuur van de tabel wel handig gedefinieerd is.

Opdracht 1-3 Als je de instructies goed hebt opgevolgd, dan heb je een klant met een dier in de tabel opgeslagen. Maar veronderstel dat mevrouw Paay nog een tweede hondje heeft, ook een Chihuahua maar deze heeft de naam Sharleen. • Voeg opnieuw gegevens toe zodat ook Sharleen in de tabel klanten van de dierenarts wordt opgenomen. Staan er nu bepaalde gegevens dubbel in het systeem?

Afbeelding 6: Dubbele gegevens? Wat zou er gebeuren, wanneer mevrouw Paay bij de dierenarts komt en tegen de assistente zegt dat zij verhuisd is? De assistente zoekt mevrouw Paay op in het systeem en past de gegevens aan. Zou dat goed gaan?

6

1.5Een slimme tabel?

1.6 dubbele gegevens voorkomen Een systeem waar gegevens dubbel in voorkomen zal tot problemen leiden. We noemen dit redundantie. Ten eerste moet je meer typewerk verrichten omdat je de gegevens meerdere malen invoert. Maar bij wijzigingen zul je er ook steeds voor moeten zorgen, dat alle gegevens worden gewijzigd. Een dergelijk systeem zal dus een bron van fouten worden, want de kans dat je een record vergeet bij te werken is heel groot. Dit kan dus inconsistentie in de database veroorzaken.

redundantie

inconsistentie

Een ander vervelend punt is dat wanneer de dierenarts een mailing laat uitgaan, alle mensen met meer dan één dier die mailing dan ook meerdere keren ontvangen. Maar hoe kun je dat nu voorkomen? Wat je typisch doet is zorgen dat je met meerdere tabellen gaat werken. Je zorgt, dat in iedere tabel gegevens komen te staan, die een directe relatie met elkaar hebben. Om de tabellen aan elkaar te kunnen koppelen ga je met zogenaamde sleutelvelden werken.

1.7 relationele database Zoals we net hebben aangegeven, moeten we redundantie proberen te voorkomen. Daarom moeten we, zoals gezegd, meerdere tabellen definiëren. We gaan onze grote tabel nu dus splitsen en ook vastleggen hoe de tabellen een relatie met elkaar hebben. Oftewel: welk hondje hoort bij welk baasje. Maar hoe gaat dat nu in zijn werk. Laten we nog eens kijken naar het lijstje van de dierenarts. • naam • voorletters_en_voorvoegsel • adres • postcode • woonplaats • telefoon • naam_dier • soort_dier • ras • geboortedatum • gewicht • kleur Delen van deze tabel hebben betrekking op de eigenaar, terwijl andere delen de gegevens zijn van het dier. Daarom ga je nu een nieuwe tabel definiëren, zodat je twee tabellen krijgt: 1 tabel voor de eigenaren en 1 voor dieren.

Hoofdstuk 1databases

7

sleutelvelden

Opdracht 1-4 •

Maak een nieuwe tabel aan in de database dierenarts met de naam dieren.

•

Stop daarin de velden die betrekking hebben op het dier (dat zouden er 6 moeten zijn).

•

Voeg nu de gegevens van de beide hondjes van mevrouw Paay toe aan de nieuwe tabel.

•

Verwijder de velden, die betrekking hebben op het dier, uit de tabel klanten. Doe dit door, in het structuur scherm, de betreffende regels aan te vinken en dan op het rode kruisje te klikken.

1.8 sleutelvelden Als het goed is, dan bevat de tabel met gegevens van de eigenaren nu de volgende velden: • naam • voorletters_en_voorvoegsel • adres • postcode • woonplaats • telefoon

Afbeelding 7: Het verwijderen van velden uit een tabel

En de structuur van de tabel met gegevens van de dieren ziet er nu als volgt uit: • naam_dier • soort_dier • ras • geboortedatum • gewicht • kleur Goed bedacht om de grote tabel op te splitsen in twee, één apart voor de eigenaren en één apart voor de dieren. Maar als je met je dier bij de dierenarts komt, dan wil je wel dat het juiste dier bij de juiste eigenaar wordt gezocht. sleutelvelden

Dat regelen door, zoals eerder gezegd, gebruikt te maken van sleutels en sleutelvelden. Wat je gaat doen is zorgen, dat ieder record uniek te herkennen is. Bijvoorbeeld met een uniek nummer. Als leerling op school heb je ook zo'n nummer, namelijk je leerlingnummer. Op school ben jij de enige met jouw leerlingnummer, dat nummer is dus uniek op school.

primaire sleutel

vreemde sleutel

We kiezen ervoor om alle eigenaren zo'n uniek nummer te geven. Daarvoor hebben we een extra veld nodig. Dit veld noemen we klantnummer. Het veld klantnummer noemen we de primaire sleutel (primairy key) van de tabel. Een primaire sleutel is dus een veld waarin verschillende records een verschillende waarde moeten hebben. We moeten dan natuurlijk ook een extra veld aan de dieren tabel toevoegen, zodat we aan kunnen geven bij welke eigenaar het dier hoort. Ook dit veld noemen we klantnummer. Het veld klantnummer in de dierentabel is ook een sleutelveld. Dit veld heet de vreemde sleutel (foreign key). Een foreign key legt dus altijd een relatie naar een sleutelveld uit een andere tabel. Je hebt nu een koppeling gemaakt tussen de twee tabellen op basis van sleutelvelden. Het is overigens jouw verantwoordelijkheid om te zorgen dat het juiste die aan de juiste eigenaar in de database wordt gekoppeld. Dat doet de database niet voor je! Hopelijk begrijp je, dat verschillende records dezelfde waarde kunnen hebben voor de vreemde sleutel. Je kunt dus dieren hebben met hetzelfde klantnummer. Immers een klant kan meerdere dieren hebben. 8

1.8sleutelvelden

Opdracht 1-5 •

Selecteer de tabel klanten

Als je op het de tab afbeelding 8).

klikt zie je de tabelstructuur met onderaan een regel (zie

Daarmee kun je een veld toevoegen.

Afbeelding 8: Een veld toevoegen •

Klik op Start om een veld aan de tabel klanten toe te voegen.

Het veld noemen we klantnummer en is van het type INT. Belangrijk is hier dat je achter het veld Index PRIMARY selecteert en dat je AUTO_INCREMENT aan vinkt. Zie afbeelding 9.

Afbeelding 9: Het klantnummer toevoegen •

Kies daarna voor Opslaan.

•

Bekijk nu via de tab de inhoud van de tabel. Als het goed gegaan is, is er nu een kolom klantnummer bijgekomen maar die is ook meteen gevuld.

•

Voeg nu aan de tabel dieren het veld diernummer toe. Deze is van het type INT, achter Index selecteer je PRIMARY en ook hier vink je AUTO_INCREMENT aan.

•

Daarnaast voeg je aan de tabel dieren ook nog het veld klantnummer toe. Deze is ook van het type INT maar hier geef je Index en AUTO_INCREMENT niet aan!

In de vorige opgave hebben we nog iets extra's gedaan. We hebben ook de dierentabel een primaire sleutel gegeven. Dat is in ons geval niet strict noodzakelijk, maar het is wel netjes. Vandaar dat we het gedaan hebben. Hoofdstuk 1databases

9

Je hebt nu twee tabellen: klanten en dieren. In beide komt het veld klantnummer voor. Wanneer je in de tabel dieren het juiste klantnummer zet, koppel je daarmee een dier aan de klant met het betreffende klantnummer. Een veld van het type INT PRIMARY AUTO_INCREMENT bevat altijd een geheel getal (INT) en hoef je niet in te vullen. Het getal wordt namelijk automatisch opgehoogd (AUTO_INCREMENT), iedere keer als je een nieuw record aan maakt. PRIMARY betekent dat het veld de primaire sleutel is in die tabel. Daarmee is in ons geval iedere eigenaar of ieder dier uniek te herkennen.

Opdracht 1-6 Waarom is het veld klantnummer in de tabel dieren niet van het type INT PRIMARY AUTO_INCREMENT?

1.9 sleutels gebruiken Als primaire sleutel gebruiken we vaak een nummer, maar dat is niet noodzakelijk. Wel noodzakelijk is, dat primaire sleutels uniek zijn. Daarmee wordt bedoeld: er mogen geen twee records zijn waarbij de primaire sleutels gelijk zijn. In ons voorbeeld gebruiken we de primaire sleutels klantnummer en diernummer. In plaats van één veld te gebruiken als sleutelveld, kan een sleutel ook uit meerdere velden (sleutelvelden) bestaan. Wanneer een primaire sleutel uit meerdere sleutelvelden bestaat, dan moet de combinatie van de waardes in de primaire sleutelvelden uniek zijn. We gaan dit verderop terugzien. Om te begrijpen hoe en wanneer je sleutels en sleutelvelden gebruikt, moet je proberen er achter te komen welke velden van elkaar afhankelijk zijn. Stel mevrouw Brouwer heeft klantnummer 3327. Maar in het systeem staat ook een meneer Brouwer met klantnummer 1185.

functioneel afhankelijk

Bij ieder klantnummer hoort precies één (niet noodzakelijk unieke) naam. Gaan we zoeken naar klantnummer 3327 dan vinden we iemand met de achternaam Brouwer. Maar zoeken we op Brouwer, dan vinden we niet automatisch klantnummer 3327. De naam wordt dus uniek geïdentificeerd door het klantnummer. We noemen in dit voorbeeld de naam functioneel afhankelijk van het klantnummer. Het volgende wat we kunnen doen is bekijken of er meer velden functioneel afhankelijk zijn van het klantnummer. Dat zijn in ieder geval alle velden die we al in de tabel eigenaren hebben staan, zoals: • naam • voorletters en voorvoegsel • adres • postcode • woonplaats • telefoon Maar hoe zit het met de velden van de tabel dieren? Wordt (bijvoorbeeld) de naam van het dier ook uniek geïdentificeerd door het klantnummer? Met andere woorden, wanneer we het klantnummer weten, weten we dan ook de naam van het dier? Nee, want een klant kan meerdere dieren hebben.

10

1.9sleutels gebruiken

Voor dieren hebben we het sleutelveld diernummer gebruikt. Ga voor jezelf na, dat de volgende velden functioneel afhankelijk zijn van dat sleutelveld. • naam van het dier • soort dier • ras • geboortedatum • gewicht • kleur Wanneer je voor een database tabellen gaat samenstellen, ga je eerst de functionele afhankelijkheid van alle velden bepalen, waarna je de velden die functioneel afhankelijk zijn van hetzelfde sleutelveld in één tabel samenbrengt. Dit noemen we data normalisatie. Het proces dus om volgens bepaalde richtlijnen de tabellen goed in te delen.

data normalisatie

Maar daar gaan Paragraaf 1.13 en Hoofdstuk 2 over.

1.10 soorten relaties In het voorbeeld van de eigenaar met zijn/haar dier(en) was er sprake van een één-op-veel of one-to-many relatie (zie ook afbeelding 10). Dat wil zeggen: je hebt één eigenaar en die kan meerdere dieren hebben. Andersom heeft gewoonlijk een dier slechts één eigenaar. Je kunt dit dus eenvoudig regelen in zo’n database met de sleutelvelden. Wanneer een dier slechts één eigenaar heeft dan voeg je het sleutelveld van de eigenaar in bij de diergegevens als vreemde sleutel. Vanuit het dier vind je dus altijd de juiste eigenaar. Je hoeft maar naar het sleutelveld te kijken en je weet welke eigenaar bij dat dier hoort.

één-op-veel one-to-many

Afbeelding 10: One-to-many relatie

En als je wilt weten welke dieren allemaal bij een bepaalde eigenaar horen dan hoef je alleen maar te zoeken op dieren met het juiste klantnummer. Bij paarden, vooral als het gaat om dure paarden, komt het vaak voor, dat een paard meerdere eigenaren heeft. Zo spreidt men de risico’s. Maar dan is er dus geen sprake van een één op veel relatie maar van een veel-op-veel (many-to-many) relatie. Bij een eigenaar kunnen dan meerdere paarden horen maar bij een paard kunnen ook meerdere eigenaren horen. Een relationele database kan een dergelijke relatie niet zonder meer aan. Er moet dan gebruik gemaakt worden van nog een tabel die de relatie vastlegt tussen de twee andere tabellen. We noemen zo'n tabel een koppeltabel.

Hoofdstuk 1databases

11

veel-op-veel many-to-many

koppeltabel

In het volgende voorbeeld zie je dat uitgewerkt:

Afbeelding 11: Many-to-many relatie tussen twee tabellen met hulptabel Er is hier een koppeltabel toegevoegd. Als je nu van een eigenaar wilt weten welke dieren er allemaal bij horen zoek je in de koppeltabel naar het klantnummer en je krijgt dan alle records met dat klantnummer en een bijbehorende diernummer. Omgekeerd, als je wilt weten welke eigenaren allemaal bij een dier horen zoek je in de koppeltabel naar het diernummer en je krijgt alle records met dat diernummer en de bijbehorende klantnummers. In een dergelijke koppeltabel wordt meestal de combinatie van de sleutelvelden van de beide andere tabellen als sleutel gebruikt. De sleutel bestaat dus uit twee sleutelvelden, in dit geval dus klantnummer & diernummer. Daarmee is ook meteen vastgelegd dat de combinatie uniek is.

1.11 het strokendiagram

strokendiagram

Om de structuur van een database, dus de structuur van de tabellen en hun onderlinge relaties, overzichtelijk weer te geven, maken we gebruik van diagrammen. Er bestaan veel diagramsoorten. Een veel gebruikte heet ERD (Entity Relationship Diagram). Een ERD neemt echter vaak veel ruimte in beslag. Een compactere manier is het strokendiagram. Eigenlijk lijkt het strokendiagram veel op de afbeeldingen die we in dit hoofdstuk al tegenkwamen maar dan wordt de structuur van een tabel niet verticaal maar horizontaal weergegeven en geven we sleutels aan met ook met pijlen. Een horizontale pijl onder een veld geeft het sleutelveld aan. Een pijl getrokken van één veld naar een andere geeft de relatie aan. De pijlpunt wijst naar een primair sleutelveld. Voor het overzicht gebruiken we in deze module ook kleuren bij de strokendiagrammen. Op het schoolexamen worden geen kleuren gebruikt! Het kleurengebruik is hetzelfde als voorheen. •

Rood geeft aan dat er sprake is van een primaire sleutel met één sleutelveld.

•

Groen geeft aan dat er sprake is van een primaire sleutel met meerdere sleutelvelden.

•

Blauw geeft aan dat er sprake is van een vreemde sleutel.

Het strokendiagram van een hondenschool kan er dan als volgt uitzien (zie afbeelding 12):

12

1.11het strokendiagram

Afbeelding 12: Een mogelijk strokendiagram voor een hondenschool De tabel honden-kunstjes heeft een sleutelveld bestaande uit de velden hondnummer en kunstjesnummer. Deze tabel is toegevoegd omdat het hier een veel-op-veel relatie betreft (zie ook paragraaf 1.10). Het is dus een koppeltabel.

Opdracht 1-7 Maak (op papier of met een tekenprogramma, bijvoorbeeld via gliffy.com) van de structuur in Afbeelding 11 een strokendiagram. Op gliffy.com kun je een tekening maken en daar een screenshot van maken.

1.12 databases exporteren Soms wil je een database aan iemand anders geven of je wilt hem gewoon bewaren voor later buiten USB Webserver. Het kan zelfs zijn dat je je database in een ander databaseprogramma wilt importeren. Hiervoor bestaan tal van mogelijkheden. De veiligste en meest generieke manier is door de database naar SQL te exporteren. SQL, zoals je verderop zult leren, is een taal die alle relationele databases begrijpen. Het exporteren van een database naar SQL doe je als volgt. Druk in PHPMyAdmin startpagina op de Exporteer knop. Deze zit in het menu aan de bovenkant van de pagina zoals je kunt zien in de volgende figuur. Dit is een voorbeeld uit versie 8.0 van USB Webserver, in hogere versies kan het er net even iets anders uitzien.

Je krijgt dan de keuze tussen snel en uitgebreid exporteren. Bij snel exporteren worden alle databases standaard geëxporteerd. Meestal wil je echter maar één database exporteren. In dat geval moet je voor uitgebreid exporteren kiezen. Je krijgt dan wat je in de volgende figuur ziet. Hoofdstuk 1databases

13

Ook dit is een voorbeeld uit versie 8.0. Het kan zijn dat er minder, meer of andere databases boven in het scherm staan.

Het enige dat je nu hoeft te doen is de juiste database te selecteren. Je kunt ook nu meerdere databases selecteren, maar doorgaans sla je iedere database individueel op. Als je de database hebt geselecteerd, dan klik je helemaal rechts onderin op de Start knop. Vervolgens krijg je een dialoog waarin je aan kunt geven waar je het bestand op wilt slaan. Het is verstandig het bestand de extensie sql te geven zodat je weet dat er SQL code in staat.

14

1.12databases exporteren

Opdracht 1-8 Exporteer de database dierenarts naar een SQL bestand.

Afbeelding 13: nog een strokendiagram van een database

Opdracht 1-9 (optioneel, mag je overslaan) Maak de structuur van het strokendiagram uit Afbeelding 13 na in USB Webserver. Neem als naam voor de database groothandel. Je hoeft alleen de structuur te maken, dus je hoeft geen data in te vullen.

1.13 databases normaliseren In het volgende hoofdstuk gaan we het hebben over het normaliseren van databases ook wel data normalisatie genoemd. Deze techniek wordt gebruikt om een database goed te structureren. Een goed genormaliseerde database is belangrijk om problemen bij het updaten van de database te voorkomen. Stel je wilt een muziek database maken en je zet al je gegevens in één grote tabel. Eén van de velden in de tabel zal het genre van de muziek zijn. Je hebt bijvoorbeeld zo'n 10000 nummers om in de database te zetten. Je luistert echter maar naar een stuk of 10 genres. Je moet dan dezelfde genrenaam heel vaak intypen, want bij elk nummer moet je het genre vermelden. Dat is niet alleen saai, maar je zult ook makkelijk een typefoutje maken. In een goed genormaliseerde database zijn er trucjes om te voorkomen dat je steeds de genrenaam in hoeft te typen. Of je hebt in een salarisdatabase met daarin twee mensen die Peter Verbiest heten. Stel dat één van hen een salarisverhoging krijgt. Welk record moet je dan aanpassen? In een goed genormaliseerde database is dat geen probleem, in een slecht genormaliseerde wel.

Hoofdstuk 1databases

15

normaliseren data normalisatie

Wat ook kan gebeuren is dat een winkel stopt met het verkopen van een product. Dit product moet dan verwijderd worden uit de database van de winkel. Hoe zorg je ervoor dat alle verwijzingen naar dat artikel in de database goed opgeruimd worden zodat je geen vervuiling achterlaat bij het updaten? Ook dit is geen probleem in een goed genormaliseerde database en in een slecht genormaliseerde vaak wel.

normaalvormen

In de voorgaande paragrafen zijn we stiekem al bezig geweest met het normaliseren door onze gegevens over meerdere tabellen te verdelen. Daar deden we dat op gevoel, maar in de praktijk worden hier strikte regels voor gebruikt. Deze regels zijn ondergebracht in diverse normaalvormen. Er bestaan 6 normaalvormen, maar er worden er meestal maar 3 gebruikt. Wat voor iedereen belangrijk is om te weten, is dat databases op de juiste manier genormaliseerd moeten zijn en wel om de volgende redenen:

redundantie

•

Het zorgt ervoor dat er zo min mogelijk redundantie (overbodige gegevens) in een database voorkomt.

consistentie

•

Het zorgt ervoor dat een database consistent (alle gegevens en hun relaties onderling kloppen met elkaar, ze bevatten dus geen tegenspraken) blijft bij het updaten.

16

1.13databases normaliseren

2 data normalisatie (vwo) Data normalisatie of database normalisatie is een techniek om de gegevens die je in een relationele database wilt opslaan op een juiste manier in tabellen te groeperen. Dit is belangrijk want het opvragen van informatie uit een database gaat het beste als de database goed genormaliseerd is. Maar daarnaast kan data normalisatie voorkomen dat gegevens dubbel worden opgeslagen. Het gevolg van normalisatie is dat we een database krijgen die geen redundantie (overbodige gegevens) of inconsistenties (elkaar tegensprekende data) bevat. Wanneer we alle gegevens die we in een database willen opslaan zonder enige structuur bij elkaar voegen zeggen we dat de gegevens zich in de nulde normaalvorm bevinden. Zo bevond het lijstje in paragraaf 1.7 zich in de nulde normaalvorm omdat er nog helemaal geen structuur in de gegevens aangebracht was. Aan de hand van voorbeelden zullen de eerste drie normaalvormen hier behandeld worden. We maken daarbij gebruik van een denkbeeldige hondenschool waar de honden kunstjes leren. Op nl.wikipedia.org/wiki/Databasenormalisatie vind je nog meer voorbeelden van deze normaalvormen.

2.1 eerste normaalvorm Ieder veld bestaat uit een enkele waarde (geen combinatie van waarden) en iedere tabel mag geen velden bevatten die herhaald worden Eigenlijk staan hier twee eisen die we aan de gegevens stellen voor ze voldoen aan de eerste normaalvorm. Allereerst gaat het erom dat ieder veld uit een enkele waarde bestaat, dus niet een combinatie van waarden. Kijk eens naar de volgende tabel met gegevens: Hond

Kunstje 1

Kunstje 2

Bouvier Rakker

Zitten

Af gaan

Duitse herder Snuf

Pootje geven Zitten

Kunstje 3

…...........

Kunstje 25

Rollen

Je ziet hier dat het veld Hond uit twee waarden bestaat, namelijk het ras en de naam van de hond. Daarmee voldoet de tabel al niet aan de eerste normaalvorm. Dit is ook wel begrijpelijk. Stel je eens voor dat je in een grote tabel naar een hond met de naam Snuf wilt gaan zoeken. Hoofdstuk 2data normalisatie (vwo)

17

Dan is het handig als die tabel gesorteerd is op alfabet. Maar dat wordt lastig in zo’n samengesteld veld. Door voor iedere waarde (in dit geval dus ras en naam) een apart veld te maken, kun je in veel gevallen gemakkelijker zoeken en bewerkingen op de gegevens uitvoeren. De tweede eis is dat een tabel geen velden mag bevatten die herhaald worden. Maar ook dat gebeurt hier. Je kunt tot 25 kunstjes in deze tabel kwijt. Dus ook daarom voldoet hij niet aan de eerste normaalvorm. Ook dit wordt verwarrend zoeken. Want als ik wil weten welke honden op commando kunnen zitten zal ik per hond alle 25 velden moeten controleren. Willen we aan de eerste normaalvorm voldoen dan moeten we dus een aparte hondentabel en een aparte kunstjestabel maken. Door middel van sleutelvelden worden die tabellen met elkaar verbonden. Allereerst de hondentabel. Die is meteen maar uitgebreid met meer gegevens van de honden. Hondnummer

Naam

Ras

Kennelnaam

Kenneladres

Kennelplaats

101

Rakker

Bouvier

Het oude bos

Boschlaan 45

Boschoord

102

Snuf

Duitse herder

De speurder

Reukstraat 1

Geurdorp

En dan hier de kunstjestabel: Hondnummer

Kunstje

Wanneer geleerd

101

Zitten

04-12-2005

101

Af gaan

03-01-2006

101

Rollen

28-01-2006

102

Pootje geven

12-01-2007

102

Zitten

24-03-2007

Het hondnummer in de hondentabel is het sleutelveld (aangeduid met een rode kleur) van die tabel waaraan het hondnummer in de kunstjestabel is gerelateerd. Het hondnummer in de kunstjestabel noemen we een foreign key of referentiesleutel. Door het op deze manier scheiden van gegevens in meerdere tabellen, kunnen we sneller zien of Rakker inderdaad kan rollen. Daarvoor kijken we of het hondnummer van Rakker ook voorkomt bij het kunstje rollen. Hoewel we nu een aparte tabel voor de kunstjes hebben is dit een tabel waar nog geen sleutelveld gedefinieerd is. Bovendien komt in die tabel het kunstje zitten twee maal voor. Verder moeten we beseffen dat de relatie tussen honden en kunstjes een many-to-many relatie is. Immers een hond kan meerdere kunstjes onder de knie hebben maar een kunstje kan ook door meerdere honden gedaan worden. In de volgende stappen gaan we dat verbeteren.

18

2.1eerste normaalvorm

2.2 tweede normaalvorm Zorg ervoor dat ieder veld functioneel afhankelijk is van het (gehele) sleutelveld. Wanneer een sleutelveld bestaat uit meerdere velden en een veld in de tabel is slechts functioneel afhankelijk van één van die velden uit dat sleutelveld, plaats dan dat veld in een afzonderlijke tabel. De kunstjestabel bevat nog geen sleutelveld. Normaliter moet een tabel altijd een sleutelveld (of een combinatie van velden die gezamenlijk functioneren als een sleutelveld) hebben om ieder record uniek te kunnen identificeren. We kunnen dit realiseren door aan de tabel het veld kunstjesnummer toe te voegen. De tabel gaat er dan als volgt uitzien: Hondnummer

Kunstjesnummer

Kunstje

Wanneer geleerd

101

1

Zitten

04-12-2005

101

2

Af gaan

03-01-2006

101

3

Rollen

28-01-2006

102

4

Pootje geven

12-01-2007

102

1

Zitten

24-03-2007

In de kunstjestabel wordt het sleutelveld nu gevormd door de combinatie van hondnummer en kunstjesnummer. Het is niet voldoende alleen kunstjesnummer te nemen als sleutelveld immers het kunstje zitten (nummer 1) komt twee maal voor en dat is bij een sleutelveld niet toegestaan. Door dit veld te combineren met het hondnummer krijgen we wel een unieke combinatie. Het kunstje is functioneel afhankelijk van het kunstjesnummer, maar niet van het hondnummer. Immers het kunstje Zitten hoort bij kunstjesnummer 1 maar niet bij hondnummer 101. Want ook hondnummer 102 kan Zitten. Daarmee voldoet deze tabel niet aan de tweede normaalvorm. Veronderstel nu eens, dat je de kunstjesnaam Zitten wilt veranderen in Zit. Je zult dan de hele kunstjestabel af moeten lopen om overal waar Zitten staat, de wijziging aan te brengen. update-probleem

Dit noemen we het update-probleem. Of veronderstel eens, dat de enige hond die op zijn voorpootjes kon lopen bij het uitoefenen van dit kunstje niet goed uitkeek en werd overreden door een vrachtwagen. Met het verwijderen van de combinatie hond - kunstje, verwijder je ook het kunstje. M.a.w. het kunstje bestaat niet meer.

delete-probleem

Dit noemen we het delete-probleem. Om deze problemen te voorkomen moeten we zorgen, dat onze tabellen voldoen aan de tweede normaalvorm en dat doen we door ervoor te zorgen dat de velden die niet functioneel afhankelijk zijn van het gehele sleutelveld in een aparte tabel worden geplaatst. In ons voorbeeld heeft dit twee tabellen tot gevolg: de tabel kunstjes, waarin een verzameling kunstjes wordt opgeslagen en de tabel honden-kunstjes, waarin wordt aangegeven welke hond een bepaald kunstje kent. Deze tabel legt dus een koppeling tussen de kunstjes en de honden. En wanneer we dan de naam van een kunstje veranderen, is dat meteen voor de hele database geregeld. We krijgen dus nu de volgende tabellen:

Hoofdstuk 2data normalisatie (vwo)

19

Hondentabel Hondnummer

Naam

Ras

Kennelnaam

Kenneladres

Kennelplaats

101

Rakker

Bouvier

Het oude bos

Boschlaan 45

Boschoord

102

Snuf

Duitse herder

De speurder

Reukstraat 1

Geurdorp

Honden-Kunstjestabel Hondnummer

Kunstjesnummer

Wanneer geleerd

101

1

04-12-2005

101

2

03-01-2006

101

3

28-01-2006

102

4

12-01-2007

102

1

24-03-2007

Kunstjestabel Kunstjesnummer

Kunstje

1

Zitten

2

Af gaan

3

Rollen

4

Pootje geven

2.3 derde normaalvorm Verwijder velden die niet functioneel afhankelijk zijn van een sleutelveld De hondentabel voldoet aan de eerste normaalvorm want hij bevat geen zich herhalende velden. Hij voldoet ook aan de tweede normaalvorm omdat hij geen sleutelveld heeft die bestaat uit meerdere sleutels. Maar het sleutelveld is het hondnummer, terwijl de kennelnaam, het kenneladres en de kennelplaats alleen betrekking hebben op de kennel en niet op de hond. Om aan de derde normaalvorm te voldoen, moeten ook deze gegevens in een aparte tabel worden ondergebracht. Aangezien ze betrekking hebben op de kennel waar de hond vandaan komt, zal er een veld kennelnummer moeten worden toegevoegd. De reden voor een dergelijke splitsing is dezelfde als die bij de second normal form. We willen de update en delete problemen vermijden. Veronderstel maar eens, dat er op een bepaald moment geen honden van kennel 'De woeste hoeve' naar de hondenschool gaan. Dat zou betekenen, dat de gegevens van die kennel dan ook niet meer in het systeem voorkomen. We krijgen nu de volgende tabellen:

20

2.3derde normaalvorm

Hondentabel Hondnummer

Naam

Ras

Kennelnummer

101

Rakker

Bouvier

445

102

Snuf

Duitse herder

22

Kenneltabel Kennelnummer

Kennelnaam

Kenneladres

Kennelplaats

445

Het oude bos

Boschlaan 45

Boschoord

22

De speurder

Reukstraat 1

Geurdorp

Honden-Kunstjestabel Hondnummer

Kunstjesnummer

Wanneer geleerd

101

1

04-12-2005

101

2

03-01-2006

101

3

28-01-2006

102

4

12-01-2007

102

1

24-03-2007

Kunstjestabel Kunstjesnummer

Kunstje

1

Zitten

2

Af gaan

3

Rollen

4

Pootje geven

Naast deze drie normaalvormen zijn er nog een aantal maar in de meeste gevallen is het voldoende als de tabellen van een database zijn ingedeeld volgens de eerste, de tweede en de derde normaalvorm.

Hoofdstuk 2data normalisatie (vwo)

21

2.4 toepassen In de volgende opdracht pas je het gebruik van de normaalvormen toe.

Opdracht 2-1 We gaan de database van de dierenarts uitbreiden. Maak van de volgende gegevens zinvolle tabellen voor het samenstellen van de database. Zorg ervoor, dat je tabellen voldoen aan de eerste, tweede en derde normaalvorm. • • • • • • • • • • • • • • • • • • • • • • • •

naam voorletters en voorvoegsel adres postcode woonplaats telefoon naam van het dier soort dier ras geboortedatum gewicht kleur behandelingsdatum tijdstip behandeling omschrijving van de behandeling (vrije tekstveld, in MySQL kies type TEXT) artikel (per behandeling kunnen meerdere artikelen worden toegediend of meegegeven) registratienummer van het artikel verpakkingseenheid van het artikel eventuele vervolgbehandeling van het artikel (bijvoorbeeld herhalingsentingen) datum van de vervolgbehandeling verrichting (bijvoorbeeld een operatie) anamnese (beschrijving van het ziekte beeld) prijs van het artikel prijs van de verrichting

Indien je denkt, dat er nog meer velden bij moeten, dan kun je die toevoegen.

Opdracht 2-2 Maak van de onderverdeling die je gemaakt hebt in de vorige opdracht een strokendiagram. Je kunt dit op papier doen of met gliffy.com.

Opdracht 2-3 Maak de structuur van het strokendiagram dat je in de vorige opgave gemaakt hebt na in USB Webserver. Neem als naam voor de database hondenschool. Je hoeft alleen de structuur te maken, dus je hoeft geen data in te vullen.

22

2.4toepassen

3 SQL Als je, zoals in de vorige twee hoofdstukken, jouw gegevens volgens de regels van de data normalisatie hebt ingedeeld, kun je met SQL die gegevens opvragen, wijzigen en controleren. De SQL opdrachten zijn onder te verdelen in drie soorten: •

SQL-DDL, de SQL Data Definition Language

•

SQL-DML, de SQL Data Manipulation Language

•

SQL-DCL, de SQL Data Control Language.

De belangrijkste voor ons is de SQL-DML. De SQL-DLC zal hier niet worden behandeld.

3.1 Data Definition Language Toen je (zie bladzijde 5) de tabel klanten in de database dieren aanmaakten zag je dat PHPMyAdmin de volgende opdracht genereerde: CREATE TABLE `dierenarts`.`klanten` ( `naam` VARCHAR( 25 ) NOT NULL , `voorletters_en_voorvoegsel` VARCHAR( 15 ) NOT NULL , `adres` VARCHAR( 25 ) NOT NULL , `postcode` VARCHAR( 7 ) NOT NULL , `woonplaats` VARCHAR( 25 ) NOT NULL , `telefoon` VARCHAR( 15 ) NOT NULL , `naam_dier` VARCHAR( 25 ) NOT NULL , `soort_dier` VARCHAR( 15 ) NOT NULL , `ras` VARCHAR( 15 ) NOT NULL , `geboortedatum` DATE NOT NULL , `gewicht` DECIMAL( 7, 3 ) NOT NULL , `kleur` VARCHAR( 15 ) NOT NULL ) ENGINE = MYISAM ; CREATE TABLE is één van de SQL-DDL opdrachten. Met de DDL opdrachten kun je SQL databases en tabellen laten aanmaken. Maar je kunt ze ook wijzigen en verwijderen. De belangrijkste SQL-DDL opdrachten zijn: CREATE TABLE, ALTER TABLE en DROP TABLE. (Er zijn overigens meerdere Data Definition Languages, bijvoorbeeld voor XML, vandaar de toevoeging SQL ervoor.) De SQL-DDL opdrachten kun je, zoals je al gezien hebt, door een programma als PHPMyAdmin laten uitvoeren zonder echt iets te weten van de opdrachten zelf. Dat is lekker gebruiksvriendelijk. Maar anders is het met de SQL-DML.

Hoofdstuk 3SQL

23

3.2 Data Manipulation Language SQL-DML bestaat uit de opdrachten SELECT, INSERT, UPDATE en DELETE. Met INSERT voeg je gegevens toe, met UPDATE wijzig je gegevens en met DELETE verwijder je gegevens. Maar SELECT is de belangrijkste. Daarmee kunnen we gegevens uit de tabellen opvragen. Om goed te oefenen met SQL kunnen we natuurlijk een groot oefenbestand aanmaken. Handiger is het gebruik te maken van een reeks SQL opdrachten die in een bestand zitten. Bekijk het bestand winkel.sql (deze staat in de ELO in het databases ZIP bestand) maar eens met Notepad++. Je ziet dan een aantal SQL opdrachten waarmee eerst de database wordt aangemaakt, daarna de tabellen en die worden ook met behulp van een SQL opdracht gevuld met gegevens. Een dergelijk bestand noemen we een SQL dump. Met MySQL kun je ook zo’n dump maken waarmee je dus een back-up van jouw gegevens hebt. Maar nu gebruiken we het om de gegevens in MySQL te importeren.

Opdracht 3-1 •

Start PHPMyAdmin

•

Maak een database aan genaamd winkel, je hoeft niet aan te geven hoeveel tabellen je in de database wilt hebben.

•

Klik op de tab

•

Klik op bestand kiezen en selecteer het bestand winkel.sql

•

Klik op Start

Als het goed gegaan is krijg je de melding: Import is geslaagd, 7 query's uitgevoerd. Dat betekent dat we nu de beschikking hebben over een bestand om mee te oefenen. Maar voor we aan de slag gaan is het goed nog even te kijken naar de structuur van de tabellen die we nu in de database winkel hebben. Want om goed te kunnen oefenen moet je de velden kennen. Door het bestand winkel.sql werden met de CREATE TABLE opdracht de volgende twee tabellen gemaakt: klanten

bestelde_artikelen

klantnummer

klantnummer

voornaam

order_datum

achternaam

artikel

woonplaats

hoeveelheid

provincie

prijs

In de SELECT opdrachten zullen we gebruik maken van de namen van de velden.

24

3.2Data Manipulation Language

3.3 SELECT De SELECT opdracht wordt gebruikt om zoekopdrachten aan de database te geven en het resultaat van die zoekopdracht te laten zien. De SELECT opdracht heeft vijf bijbehorende deelopdrachten die gebruikt kunnen worden bij het zoeken, maar alleen de FROM deelopdracht is verplicht. Ieder van deze vijf kan weer opties, parameters enz. hebben. Hier een overzicht van de SELECT opdracht. SELECT [ALL | DISTINCT] veld1[,veld2] FROM tabel1[,tabel2] [WHERE voorwaarden] [GROUP BY veld-lijst] [HAVING voorwaarden] [ORDER BY veld-lijst [ASC | DESC] ] Staat iets tussen rechte haken zoals [,veld2] dan is dat niet verplicht. Staat er een verticaal streepje tussen zoals bij [ALL | DISTINCT] dan moet je uit één van beide kiezen (als je die optie gebruikt). De diverse mogelijkheden van de SELECT opdracht zullen in de volgende paragrafen aan de orde komen. Voorbeeld: Veronderstel dat je uit de tabel bestelde_artikelen die artikelen wilt zien waarvan de prijs lager dan € 10 is. De opdracht luidt dan als volgt: SELECT * FROM bestelde_artikelen WHERE prijs < 10 Met SELECT * wordt aangegeven dat je alle velden wilt zien. De * noemen we een wildcard of joker. Achter de SELECT kun je ook een deel van de velden aangeven, bijvoorbeeld SELECT artikel. Met FROM bestelde_artikelen geef je aan uit welke tabel de gegevens gehaald moeten worden. Tenslotte zie je dat met WHERE prijs <10 de voorwaarde gegeven wordt waaraan de informatie moet voldoen. Het is belangrijk te beseffen wat het resultaat van een SELECT opdracht is. Het resultaat is namelijk weer een tabel. Je past de SELECT dus toe op één of meerdere tabellen en wat het DBMS je teruggeeft is een tabel. Het is erg belangrijk dat je dit goed beseft en onthoudt voor als we SELECT op meerdere tabellen gaan toepassen.

Opdracht 3-2 Test dit als volgt uit: •

Klik op de tab

•

Er verschijnt het scherm: Draai SQL query/qureies op database winkel:

•

Vul in dat scherm in: SELECT * FROM bestelde_artikelen WHERE prijs <10

•

Druk op Start

Als het goed is zie je (als je hetzelfde bestand hebt gebruikt) de gegevens van 5 artikelen waarvan de prijs lager is dan 10. Hoofdstuk 3SQL

25

Samengevat: Achter SELECT komen dus de velden die je wilt opvragen. Achter FROM komen de tabellen waaruit de gegevens moeten komen. Achter WHERE komen de voorwaarden waaraan de gegevens moeten voldoen. Bij een WHERE opdracht gebruik je vaak een vergelijkingsoperator. Hieronder een overzicht van de operatoren die je kunt gebruiken: Operator

Betekenis

=

is gelijk aan

>

is groter dan

<

is kleiner dan

>=

is groter dan of gelijk aan

<=

is kleiner dan of gelijk aan

<> of !=

is ongelijk aan

LIKE

vergelijkt (een deel van) de tekst

De meeste van de operatoren in deze tabel zullen wel duidelijk zijn. LIKE wordt hieronder met een voorbeeld toegelicht. Veronderstel weer de tabel artikelen. SELECT * FROM bestelde_artikelen WHERE artikel LIKE 'Pa%' Hier wordt een selectie gemaakt op basis van het veld artikel. Ieder artikel waarvan de naam begint met Pa komt in de selectie. Dus of het nu om een Paraplu gaat of om een Parachute, het procentteken geeft aan dat wat na Pa komt alles mag zijn. Het is dus ook prima als er niets achter Pa staat. Het procentteken noemen we, net als de *, een wildcard of joker. Het procentteken mag ook vóór de vergelijkingstekst komen. Bijvoorbeeld: SELECT * FROM bestelde_artikelen WHERE artikel LIKE '%ch%' In dit geval zal hij o.a. Sneeuwschoenen maar ook Luchtbed selecteren omdat in beide het tekstgedeelte ch voorkomt. Naast het procentteken is er nog een andere wildcard of joker, namelijk de underscore. Laten we deze ook toelichten aan de hand van een voorbeeld: SELECT * FROM bestelde_artikelen WHERE artikel LIKE 'Para__u%' In de bovenstaande query staan twee underscores. Dat wil zeggen dat ieder artikel waarvan: • de naam begint met Para, • dan precies 2 willekeurige tekens bevat (exact 1 voor iedere underscore), • dan een u bevat en • dan gevolgd wordt door 0 of meer tekens (vanwege het procentteken) voorkomt in de selectie. 26

3.3SELECT

Dus wederom komen zowel Paraplu als Parachute voor in de selectie, maar bijvoorbeeld Parasol niet. ALL en DISTINCT worden gebruikt om alle of juist unieke records op te vragen. ALL is de standaard (dat noemt men ook wel default waarde); als je hier niets vermeldt zal hij alle records laten zien die aan de selectiecriteria voldoen. Dus eigenlijk hoef je ALL niet te gebruiken. Gebruik je DISTINCT dan geeft je opdracht dat de informatie die je opvraagt uniek moet zijn. Als je bijvoorbeeld wilt weten uit welke provincies de klanten komen dan kun je de volgende select opdracht laten uitvoeren: SELECT DISTINCT provincie FROM klanten Dit geeft een heel andere uitvoer dan: SELECT provincie FROM klanten

Opdracht 3-3 Test beide hierboven gegeven query's uit en bekijk het verschil in uitvoer.

Opdracht 3-4 a. Geef de SELECT opdracht waarmee je een lijst krijgt van alle artikelen die door klantnummer 10449 zijn besteld. Laat daarbij het klantnummer, de naam van het artikel en de prijs zien. b. Geef de SELECT opdracht waarmee je alle velden ziet van de bestellingen van een Tent. c. Geef de SELECT opdracht waarmee je de velden klantnummer, order_datum en artikel ziet voor alle artikelen die beginnen met de letter “S”. d. Geef de SELECT opdracht waarmee je de unieke artikelen laat zien. e. Geef de SELECT opdracht waarmee je een lijst krijgt van alle klanten die in een provincie wonen die begint met de letter “F”. f.

Geef de SELECT opdracht waarmee je het klantnummer of de klantnummers krijgt die als voornaam “Jan” hebben.

g. Geef de SELECT opdracht waarmee je een lijst krijgt van alle artikelen die vier tekens in hun naam hebben. Laat hierbij het klantnummer, de naam van het artikel en de prijs zien.

3.4 AND en OR Als we WHERE gebruiken willen we daar vaak meer dan één voorwaarde achter zetten. De opgevraagde gegevens moeten dan aan meerdere voorwaarden voldoen. Soms moeten ze aan alle voorwaarden voldoen maar soms ook aan tenminste één van de voorwaarden. Door gebruik te maken van AND en OR, twee zogenaamde logische operatoren, kunnen we dat realiseren. Bij AND geldt dat de beide voorwaarden die links en rechts van de AND staan waar moeten zijn, bij OR geldt dat één van de beide voorwaarden waar moet zijn.

Hoofdstuk 3SQL

27

Veronderstel dat je een lijst wilt hebben van paraplu’s vanaf 5 euro. Dat kun je opvragen met de volgende opdracht: SELECT * FROM bestelde_artikelen WHERE artikel = "Paraplu" AND prijs >= 5 Om het lezen van dergelijke voorwaarden gemakkelijker te maken mag je er als volgt haakjes om heen zetten: WHERE (artikel = "Paraplu") AND (prijs >= 5) Haakjes zijn helemaal handig als je meerdere voorwaarden hebt en je wilt de voorrang bepalen van de afzonderlijke voorwaarden. Dit is vooral het geval wanneer je AND en OR wilt combineren. Hieronder een voorbeeld van het gebruik va OR: SELECT * FROM bestelde_artikelen WHERE artikel = "Paraplu" OR artikel = "Tent" Bij deze selectie krijg je alle paraplu’s en alle tenten.

Opdracht 3-5 Wat levert deze opdracht op? SELECT * FROM bestelde_artikelen WHERE artikel = "Paraplu" OR prijs >= 5

Opdracht 3-6 a. Geef de SELECT opdracht waarmee je het klantnummer, de order_datum en het artikel krijgt uit de tabel bestelde_artikelen behalve van de sneeuwschoenen en van de oorwarmers. b. Geef de SELECT opdracht waarmee je alle items en hun prijs krijgt die beginnen met de letters ‘S’, ‘P’ of ‘F’. c. Geef de SELECT opdracht waarmee je alle klanten selecteert die uit Friesland of uit Gelderland komen. d. Geef de SELECT opdracht waarmee je alle klanten selecteert die niet uit Friesland of uit Gelderland komen.

3.5 IN en BETWEEN Met IN wordt bekeken of het veld voor het woord IN ook voorkomt in de lijst na het woord IN. Zoeken we bijvoorbeeld de klanten in Almelo, Utrecht en Sneek dan kunnen we dat doen op deze manier: SELECT * FROM klanten WHERE woonplaats IN ('Almelo', 'Utrecht', 'Sneek')

28

3.5IN en BETWEEN

Je had dit ook op een andere manier op kunnen lossen namelijk met OR. Onderstaande SELECT opdracht levert hetzelfde resultaat: SELECT * FROM klanten WHERE woonplaats = 'Almelo' OR woonplaats = 'Utrecht' OR woonplaats = 'Sneek') Je ziet dat door het gebruik van IN de opdracht een stukje korter is. Je kunt in plaats van IN ook NOT IN gebruiken. In het voorbeeld hieronder worden dan alle klanten geselecteerd die juist niet in één van die plaatsen wonen. SELECT * FROM klanten WHERE woonplaats NOT IN ('Almelo', 'Utrecht', 'Sneek') Met BETWEEN bekijk je of het veld waar je op zoekt tussen twee waarden zit. Bijvoorbeeld Zoek je de bestelde artikelen die tussen de 10 en 40 euro kostten dan kun je dat als volgt doen: SELECT * FROM bestelde_artikelen WHERE prijs BETWEEN 10 AND 40 En met: SELECT * FROM klanten WHERE woonplaats BETWEEN 'Almelo' AND 'Delfstrahuizen' krijg je de gegevens van de klanten die in de woonplaatsen Almelo of Delfstrahuizen wonen of die in woonplaatsen wonen die daar alfabetisch tussenin liggen. Net als bij IN kun je een opdracht met BETWEEN ook anders schrijven: SELECT * FROM bestelde_artikelen WHERE prijs >= 10 AND prijs <= 40 Zoals te verwachten viel kun je ook hier voor BETWEEN het woord NOT gebruiken als je juist het tegenovergestelde wilt selecteren. SELECT * FROM bestelde_artikelen WHERE prijs NOT BETWEEN 10 AND 40

Opdracht 3-7 a. Selecteer de datum, het artikel en de prijs van de bestelde_artikelen tabel voor ieder record met een prijs tussen 10.00 en 80.00. b. Selecteer de voornaam, de stad en de provincie van de klanten uit de provincies Friesland, Flevoland, Noord-Holland, Zuid-Holland en Utrecht. c. Selecteer alle velden van de bestelde artikelen waarvan de hoeveelheid ligt tussen 1 en 3. Doe dit tweemaal, zowel met IN als met BETWEEN. d. Als opdracht c alleen nu het tegenovergestelde, dus met NOT.

Hoofdstuk 3SQL

29

3.6 de aggregaat functies Aggregaat functies (functies die dingen samen rapen) worden gebruikt om te kunnen rekenen aan geaggregeerde veldwaardes (dat wil zeggen alle waarden van een bepaald veld in een tabel of groep) die met de SELECT opdracht worden opgehaald. Het gaat dan meestal om optellen, gemiddelden enzovoorts. Functie

Betekenis

MIN

geeft de laagste waarde van een kolom of aggregaat

MAX

geeft de hoogste waarde van een kolom of aggregaat

SUM

geeft het totaal van een kolom of aggregaat

AVG

geeft het gemiddelde van een kolom of aggregaat

COUNT

geeft het aantal records in een aggregaat

Voorbeelden: De gemiddelde prijs van de artikelen in een tabel kun je opvragen met: SELECT AVG(prijs) FROM bestelde_artikelen In het volgende voorbeeld wordt de gemiddelde prijs gevraagd van alle Tenten in de tabel: SELECT AVG(prijs) FROM bestelde_artikelen WHERE artikel = 'Tent' En op deze manier tel je het aantal bestellingen per artikel: SELECT COUNT(*) FROM bestelde_artikelen

Opdracht 3-8 a. Geef de SELECT opdracht waarmee je uit de tabel bestelde_artikelen de maximum prijs van de artikelen vindt. (Het gaat hier dus om de prijs van het duurste artikel. Het gaat er niet om dat je erbij vermeldt welk artikel het betreft). b. Geef de SELECT opdracht waarmee je de gemiddelde prijs krijgt van alle artikelen die in december zijn besteld. c. Geef de SELECT opdracht waarmee je het totaal aantal bestellingen van zaklantaarns krijgt. d. Geef de SELECT opdracht waarmee je van alle bestelde tenten de laagste prijs krijgt. Laat alleen de prijs zien! e. Geef de SELECT opdracht waarmee je het aantal bestellingen van januari krijgt. f.

Geef de SELECT opdracht waarmee je het aantal klanten krijgt waarvan de achternaam met een ’G’ begint.

g. Geef de SELECT opdracht die je de gemiddelde prijs van de zaklantaarns oplevert.

30

3.6de aggregaat functies

3.7 wiskundige berekeningen Binnen SQL kun je ook een aantal wiskundige berekeningen opnemen in jouw opdracht zoals: Operator

Betekenis

+

optellen

-

aftrekken

*

vermenigvuldigen

/

delen

%

modulus

Maar er zijn nog meer berekeningen die je binnen SQL kunt doen. Hieronder de tabel met een aantal voorbeelden: Functie

Betekenis

ABS(x)

geeft de absolute waarde van x

SIGN(x)

geeft het teken van x in de vorm van -1 voor een negatief getal, 0 voor 0 en 1 voor een positief getal

MOD(x,y)

geeft de rest bij deling van x door y. Dit is hetzelfde als x%y

POWER(x,y)

geeft de waarde van x tot de macht y

ROUND(x)

rondt x af op het dichtstbijzijnde gehele getal

ROUND(x,d)

rondt x af op een getal met d decimalen

SQRT(x)

geeft de wortel van x

Vergelijk het resultaat maar eens van de volgende drie SELECT opdrachten: SELECT AVG(prijs) FROM bestelde_artikelen SELECT ROUND(AVG(prijs)) FROM bestelde_artikelen SELECT ROUND(AVG(prijs),2) FROM bestelde_artikelen

Opdracht 3-9 a. Selecteer het artikel en de eenheidsprijs voor ieder artikel in de bestelde_artikelen tabel. (Hint: deel hiervoor de prijs door de hoeveelheid.) b. Geef de SELECT opdracht om de afgeronde prijs van de eenwielers te bepalen. c. Geef de SELECT opdracht om de afgeronde som van de prijzen van de eenwielers te bepalen. d. Geef de SELECT opdracht om te bepalen wat het kleinste bedrag (prijs) is uit de bestelde_artikelen tabel dat gelijk aan of groter dan 100.00 is.

Hoofdstuk 3SQL

31

3.8 ORDER BY Met ORDER BY bepaal je hoe de uitvoer gesorteerd wordt. Achter ORDER BY kunnen meerdere kolommen komen waarna eerst op de eerste kolom gesorteerd wordt en daarbinnen op de volgende kolom enz. Ook hier weer eerst een voorbeeld: SELECT * FROM bestelde_artikelen ORDER BY prijs De artikelen worden nu gesorteerd op prijs, van laag naar hoog. Wil je ze van hoog naar laag zien dan zet je er DESC achter. Zoals: SELECT * FROM bestelde_artikelen ORDER BY prijs DESC Je hebt voor het sorteren de keuze uit ASC (ascending = oplopend) en DESC (descending = aflopend) maar ASC is de default waarde. Voor cijfers betekent ASC van laag naar hoog, voor letters betekent het van A tot z. DESC sorteert dus juist andersom. Je kunt dus op meerdere kolommen sorteren. Als je eerst op hoeveelheid wilt sorteren maar daar binnenin op prijs dan doe je dat als volgt: SELECT * FROM bestelde_artikelen ORDER BY hoeveelheid, prijs

Opdracht 3-10 a. Selecteer de voornaam, de achternaam en de woonplaats van alle klanten in de klantentabel. Zorg ervoor dat de resultaten zichtbaar worden oplopend gesorteerd op basis van de achternaam. b. Als opdracht a maar nu in aflopende volgorde. c. Selecteer artikel en prijs uit alle artikelen in de bestelde_artikelen tabel waarvan de prijs hoger is dan 10.00. Sorteer de resultaten in oplopende volgorde gebaseerd op de prijs. d. Met welke SELECT opdracht krijg je alle gegevens van de bestelde_artikelen tabel gesorteerd eerst op klantnummer en daarna aflopend op prijs?

32

3.8ORDER BY

3.9 GROUP BY De GROUP BY optie aggregeert (raapt samen) de identieke waardes van een opgegeven kolom in één rij. Een kort voorbeeld ter verduidelijking. Stel je hebt in een tabel met liedjes 16 records en je hebt 6 keer het genre rock en 10 keer het genre pop. Als je dan groepeert (GROUP BY) op genre, dan houd je maar twee records over. Dit ziet er bijvoorbeeld als volgt uit: SELECT genre FROM liedjes GROUP BY genre; Met als resultaat: genre pop rock Dit kan handig zijn als je bijvoorbeeld wilt tellen hoe vaak het genre rock in een tabel voorkomt. Wanneer je namelijk groepeert houdt het DBMS voor je bij hoeveel records bij een groep horen en dat kun je laten zien met de COUNT functie. GROUP BY is zeer geschikt om te gebruiken samen met allerlei van zulke aggregaat functies. Dit ziet er voor de liedjes dan als volgt uit: SELECT genre, COUNT(*) FROM liedjes GROUP BY genre; Met als resultaat: genre

COUNT(*)

pop

10

rock

6

Nu zelf aan de slag. Veronderstel dat je de bestelde_artikelen tabel wilt groeperen op grond van het bestelde artikel en als je dan ook nog wilt weten wat de hoogste prijs is van deze groep, dan krijg je de volgende SELECT opdracht: SELECT artikel, MAX(prijs) FROM bestelde_artikelen GROUP BY artikel; Met als resultaat: artikel

MAX(prijs)

Eenwieler

192.00

Fiets

380.00

...

...

Zaklantaarn

29.00

Zakmes

22.00

Hoofdstuk 3SQL

33

Opdracht 3-11 Voer deze opdracht in en controleer of het klopt. Zorg dat je goed snapt wat er gebeurt.

Opdracht 3-12 a. Geef de SELECT opdracht om te bepalen hoeveel klanten er in iedere staat (uniek) wonen. Laat de staat zien en het aantal mensen. (Tip: COUNT gebruik je om rijen te tellen, SUM werkt alleen op numerieke gegevens!) b. Geef de SELECT opdracht waarmee je ieder artikel en de maximum en de minimum prijs daarvan krijgt. c. Geef de SELECT opdracht om het aantal artikelen per klant en het totaal bestede bedrag te achterhalen. Doe dat door als kolommen het klantnummer, het aantal bestellingen (met COUNT) en het totaalbedrag van die bestellingen (met SUM) op te vragen. d. Als opdracht c maar nu gaat het alleen om de orders in januari.

3.10 HAVING HAVING hoort bij GROUP BY. Met HAVING leg je de voorwaarden vast die ervoor zorgen welke rijen met GROUP BY geselecteerd worden. Voorbeeld: In opdracht 27 hebben we de bestelde artikelen gegroepeerd op grond van de bestelde hoeveelheid en daarvan de hoogste prijs laten zien. Maar als we deze gegevens alleen willen zien als de hoeveelheid groter is dan 1 dan kan dat met HAVING als volgt: SELECT hoeveelheid, MAX(prijs) FROM bestelde_artikelen GROUP BY hoeveelheid HAVING hoeveelheid > 1 Denk er dus om, wanneer je GROUP BY gebruikt hoort daar HAVING bij en niet WHERE! Als je in dit voorbeeld had willen selecteren op prijs dan had je dat gewoon met WHERE moeten doen maar omdat hoeveelheid onderwerp is van het groeperen gebruik je daar HAVING.

Opdracht 3-13 a. Geef de SELECT opdracht om te bepalen hoeveel klanten er in iedere provincie (uniek) wonen. Laat de provincie en het aantal klanten zien maar alleen wanneer het aantal klanten groter is dan één. b. Geef de SELECT opdracht waarmee je ieder artikel en de maximum en minimum prijs daarvan krijgt. Maar laat de rij alleen zien als de maximum prijs groter is dan 190.00. c. Geef de SELECT opdracht om het aantal artikelen per klant en het totaal bestede bedrag te achterhalen voor die klanten die meer dan 1 artikel besteld hebben. Het gaat hier niet om de klanten die van één artikel meer dan één exemplaar bestelden maar om klanten die meerdere (verschillende) artikelen bestelden d. Geef de SELECT opdracht waarmee je ieder artikel en de maximum en de minimum prijs daarvan krijgt. Maar laat de rij alleen zien als er totaal meer dan 3 van besteld zijn. e. Geef de SELECT opdracht waarmee je ieder artikel en de maximum en de minimum prijs daarvan krijgt. Maar laat de rij alleen zien als de maximum prijs groter is dan 100.00. Zorg ervoor dat de artikelen aflopend gesorteerd zijn. 34

3.10HAVING

3.11 tabellen combineren met JOIN Misschien wel de belangrijkste SQL opdracht, de JOIN. Alhoewel het helemaal geen opdracht is zoals we ze eerder gehad hebben. Joinen is het koppelen van meerdere tabellen. We hebben hier immers te maken met een relationele database dus zullen er tussen de tabellen relaties zijn. Met JOIN gebruiken we die relaties om gegevens uit meerdere tabellen op te vragen. Het strokendiagram dat we tekenen van een database is hierbij onmisbaar. Opdracht 3-14 a. Teken het strokendiagram van de winkel database waarmee je tot nu toe gewerkt hebt. b. Is dit een goed gedefinieerde structuur? Hint: Zijn er sleutels gedefinieerd of is er genormaliseerd? Tijdens het gebruik van de bestanden en in het strokendiagram zie je dat er een klanten en een bestelde_artikelen tabel is. Beide tabellen hebben het veld klantnummer waarmee je de tabellen kunt koppelen. Dit veld is daarom belangrijk als we tabellen willen joinen. Als je een bestelling wilt verzenden heb je er niet zoveel aan als je alleen het klantnummer hebt. Je wilt dan natuurlijk ook de adresgegevens weten. En die zul je dan op moeten zoeken in de klantentabel. Dat doe je met een JOIN. Er is sprake van een JOIN als je meer dan één tabel achter FROM hebt staan. Dit is een zogenaamde impliciete JOIN. Er bestaan nog veel meer soorten JOIN constructies maar voor ons is de impliciete JOIN voldoende. Probeer onderstaande opdracht maar eens: SELECT voornaam, achternaam, woonplaats, artikel FROM klanten, bestelde_artikelen WHERE klanten.klantnummer = bestelde_artikelen.klantnummer Als het goed gegaan is krijg je de voornaam, achternaam, woonplaats en het artikel dat de betreffende klant besteld heeft. Heel belangrijk is dat je in de WHERE opdracht de relatie tussen beide tabellen aangeeft via het veld dat ze delen, in dit geval het veld klantnummer. Kortom, SQL mag alleen een regel laten zien wanneer het klantnummer in de tabel klanten gelijk is aan het klantnummer in de bestelde_artikelen tabel. Want wat gebeurt er als je dat niet doet? Probeer deze maar eens: SELECT voornaam, achternaam, woonplaats, artikel FROM klanten, bestelde_artikelen Je krijgt nu een veel langere lijst. SQL heeft iedere regel uit de klantentabel gekoppeld aan iedere regel uit de bestelde_artikelen tabel. En dat is niet nuttig voor wat we willen!

Hoofdstuk 3SQL

35

Nog één belangrijk punt. Als je probeert een veld op te vragen dat in beide tabellen voorkomt (zoals het veld waarop je de tabellen koppelt, hier dus klantnummer), dan moet je dat veld aanduiden (scoping) met de tabelnaam. Dit doe je door de tabelnaam gevolgd door een punt voor de veldnaam te zetten. Wil je met deze tabellen het klantnummer ook opvragen dan gaat het er dus als volgt uitzien: SELECT klanten.klantnummer, voornaam, achternaam, woonplaats, artikel FROM klanten, bestelde_artikelen WHERE klanten.klantnummer = bestelde_artikelen.klantnummer

In plaats van klanten.klantnummer had je hier ook bestelde_artikelen.klantnummer kunnen typen. Het resultaat is hetzelfde. Als je tabellen met lange namen hebt, zoals in ons geval bestelde_artikelen. Dan is het vervelend dat je steeds de tabelnaam in moet typen. Zeker als de JOIN constructies ingewikkelder worden levert dit niet alleen veel typewerk maar het wordt ook onoverzichtelijk. Dit kunnen we oplossen door zogenaamde aliassen (aliases) te gebruiken. Dat wil zeggen dat we een tabelnaam een afgekorte naam geven en die gebruiken in plaats van de volledige tabelnaam. Dat ziet er als volgt uit. SELECT K.klantnummer, voornaam, achternaam, woonplaats, artikel FROM klanten K, bestelde_artikelen BA WHERE K.klantnummer = BA.klantnummer Dat scheelt typen en maakt het overzichtelijker, of niet? Het gebruik van aliassen is niet verplicht! Let op: In plaats van te vragen naar de naam, het adres en de woonplaats van een klant, wordt vaak gevraagd naar de NAW gegevens. NAW staat voor naam (zowel voor- als achternaam), adres en woonplaats. Als b.v. het adres niet in de tabel voorkomt, dan kun je deze uiteraard niet laten zien. In een vraag kan echter nog steeds naar NAW gegevens gevraagd worden.

36

3.11tabellen combineren met JOIN

Het is belangrijk om te beseffen dat het resultaat van een query (dus ook van een query met een JOIN) gewoon een tabel is. In de voorgaande paragrafen hebben we heel veel dingen geleerd die we hebben toegepast op één tabel. Welnu, het resultaat van een JOIN is weer een tabel en daarom kunnen we er alles wat we tot nu toe hebben geleerd op toepassen. Zo kunnen we bijvoorbeeld de uitkomst van onze laatste query alfabetisch sorteren op woonplaats. Dit doen we als volgt. SELECT K.klantnummer, voornaam, achternaam, woonplaats, artikel FROM klanten K, bestelde_artikelen BA WHERE K.klantnummer = BA.klantnummer ORDER BY woonplaats Opdracht 3-15 a. Geef de SELECT opdracht om de volgende velden te laten zien: klantnummer, voornaam, achternaam, order_datum, artikel en prijs voor alles wat iedere klant heeft besteld. b. Als oefening a maar nu de resultaten aflopend gesorteerd op het veld provincie. c. Geef de SELECT opdracht om alle bestellingen van klanten uit Utrecht te laten zien. d. Geef de SELECT opdracht om alle gegevens uit de tabel klanten te laten zien van klanten die een zaklantaarn bestelden.

3.12 sub-query's (vwo) Je hebt nu de basis van SQL gezien en hopelijk begrepen. Nu gaan we het moeilijker maken door te gaan werken met sub-query's. Met wat we hiervoor hebben geleerd is het namelijk niet mogelijk om alle vragen aan een database te stellen die we eventueel zouden willen stellen. Stel we willen weten wat het hoogste aantal is dat er van enig artikel tot nu toe is besteld. We zullen dan de SUM functie moeten gebruiken om de som voor elk artikel te berekenen en daar het maximum van pakken. We zouden het volgende kunnen proberen. SELECT MAX(SUM(hoeveelheid)) FROM bestelde_artikelen GROUP BY artikel We krijgen dan de foutmelding #1111 - Invalid use of group function. SQL is blijkbaar niet slim genoeg om te snappen wat we hier bedoelen. We moeten het daarom in stappen doen. We kunnen een tabel genereren waarin alle artikelen staan met daarachter hoeveel er van besteld zijn. De query lijkt erg op de bovenstaande. SELECT artikel, SUM(hoeveelheid) FROM bestelde_artikelen GROUP BY artikel

Hoofdstuk 3SQL

37

Stel we noemen deze tabel R (van resultaat). Van tabel R is het niet moeilijk om het maximum van de hoeveelheden te bepalen. Dit doen we door de volgende query. SELECT MAX(hoeveelheid) FROM R Als we deze twee query's hebben, kunnen we die dan combineren? Inderdaad dat kan en wel door middel van sub-query's. We kunnen echter niet zomaar wat query's aan elkaar plakken. Ons DBMS moet namelijk weten hoe we de resultaattabel van onze "sum" query noemen. Laten we voor het gemak R aanhouden. Dit geven we als volgt aan. (SELECT artikel, SUM(hoeveelheid) FROM bestelde_artikelen GROUP BY artikel) AS R We hebben het bij de JOIN al over aliassen gehad en dat doen we hier weer maar dan op een andere manier. We moeten in dit geval namelijk het keyword AS gebruiken. De query zoals die hierboven staat kun je op zichzelf niet runnen. Maar we kunnen hem nu wel in de "max" query gebruiken. Dit doen we als volgt. SELECT MAX(SUM(hoeveelheid)) FROM (SELECT artikel, SUM(hoeveelheid) FROM bestelde_artikelen GROUP BY artikel) AS R Het idee van wat hierboven staat is prima maar SQL heeft moeite met de MAX(SUM(hoeveelheid)) constructie zoals we al eerder hebben gezien. Hier hebben we nogmaals een alias nodig. Het is namelijk zeer onhandig als er vreemde symbolen, zoals haakjes, in een veldnaam staan. Daarom geven we het veld SUM(hoeveelheid) een andere naam, laten we S nemen. Dit ziet er dan als volgt uit. SELECT MAX(S) FROM (SELECT artikel, SUM(hoeveelheid) AS S FROM bestelde_artikelen GROUP BY artikel) AS R En dan werkt de query en krijgen we het juiste resultaat terug, namelijk 8. Hierboven hebben we één manier gezien om met sub-query's te werken. Er zijn er echter meer. Een andere manier is om een sub-query in de WHERE te gebruiken. Wat als er meerdere artikelen waren geweest die 8 keer verkocht zijn (toevallig was het er nu maar één). Als we bijvoorbeeld niet naar het hoogste aantal maar naar het laagste aantal hadden gezocht dan hadden we het antwoord 1 teruggekregen. Maar er zijn best veel artikelen in onze database die slechts 1 keer verkocht zijn. Hoe pakken we dat aan? We hebben gezien hoe we het maximum uit een tabel halen dus dan kunnen we er ook het minimum uithalen. De onderstaande query levert dus het getal 1 op. SELECT MIN(S) FROM (SELECT artikel, SUM(hoeveelheid) AS S FROM bestelde_artikelen GROUP BY artikel) AS R

38

3.12sub-query's (vwo)

Als we ervan uitgaan dat we weten dat het minimum 1 is zouden we de volgende query kunnen gebruiken om alle artikelen af te drukken die 1 keer verkocht zijn. SELECT artikel, SUM(hoeveelheid) AS S FROM bestelde_artikelen GROUP BY artikel HAVING S = 1 Maar dat is vals spelen want het is niet gegarandeerd dat het minimum 1 is, dat is nu toevallig zo. Gelukkig weten we hoe we het minimum bepalen. Dus wat als we de twee query's combineren? Inderdaad, dat levert het gewenste resultaat en dat ziet er als volgt uit. SELECT artikel, SUM(hoeveelheid) AS S FROM bestelde_artikelen GROUP BY artikel HAVING S = (SELECT MIN(S) FROM (SELECT artikel, SUM(hoeveelheid) AS S FROM bestelde_artikelen GROUP BY artikel) AS R) Wat hier nogal gewaagd is dat is dat we tweemaal de alias S gebruiken. Gelukkig gaat dat hier goed, maar dat is niet altijd zo. Het daarom af te raden om dit zo te doen. Je kunt bijvoorbeeld S1 en S2 gebruiken dan weet je zeker dat je nooit in de problemen komt. Wat je verder moet weten is dat wanneer je een sub-query gebruikt achter een vergelijkingsoperator het resultaat van de sub-query altijd een tabel moet zijn met precies 1 record en 1 veld, feitelijk een tabel met slechts 1 waarde erin. Ziet er best complex uit die query hierboven. Kan het niet eenvoudiger? Ja, dat kan en we laten zien hoe aan het einde van deze paragraaf. Eerst ga je wat oefenen met sub-query's. Opdracht 3-16 a. Maak een query die de klantnummers geeft van de klanten die orders hebben geplaatst voor het kleinste bedrag in de database. Let op dat er dus geen klantnummers dubbel in het resultaat mogen voorkomen. b. Maak een query die de klantnummers geeft van de klanten die orders hebben geplaatst waarvan het prijs onder het gemiddelde van alle prijzen in de database ligt. c. Maak een query die teruggeeft hoeveel orders een prijs hebben die onder het gemiddelde van alle prijzen in de database ligt. d. Maak een query die teruggeeft hoeveel klanten er een order hebben geplaatst waarvan de prijs onder die van het gemiddelde van alle prijzen in de database ligt.

Hoofdstuk 3SQL

39

We hebben hierboven gekeken of iets gelijk (en in de opdrachten ook naar kleiner) is aan het resultaat van een sub-query die een tabel met 1 waarde teruggeeft. We kunnen echter ook kijken of iets in een sub-query resultaattabel zit die 1 veld maar meerdere records teruggeeft. Dit doen we door het keyword IN te gebruiken. We zouden bijvoorbeeld een lijst met namen kunnen maken van klanten die een order hebben geplaatst voor het minimale bedrag in de database. Dat ziet er als volgt uit. SELECT voornaam, achternaam FROM klanten WHERE klantnummer IN (SELECT DISTINCT klantnummer FROM bestelde_artikelen WHERE prijs = (SELECT MIN(prijs) FROM bestelde_artikelen)) Vaak zie je dat je deze constructie kunt voorkomen door de JOIN te gebruiken. Met een JOIN ziet de query, die hetzelfde resultaat oplevert als de query hierboven, er als volgt uit. SELECT voornaam, achternaam FROM klanten K, bestelde_artikelen BA WHERE K.klantnummer = BA.klantnummer AND prijs = (SELECT MIN(prijs) FROM bestelde_artikelen) De tweede oplossing met de JOIN ziet er wat code betreft eenvoudiger uit en verdient daarom in de meeste gevallen de voorkeur. Opdracht 3-17 a. Maak een query die de namen van klanten teruggeeft die een order hebben geplaatst met een prijs die boven het gemiddelde van alle prijzen in de database ligt. Je mag hierbij geen JOIN gebruiken! b. Hetzelfde als bij opdracht a maar nu moet je juist een JOIN gebruiken. Wanneer een sub-query een tabel met meerdere velden en records teruggeeft en we willen controleren of daar een record in zit dan gebruiken we het keyword EXISTS. Vaak kunnen we dit soort query's ook omschrijven naar een query met een JOIN zoals we hieronder kunnen zien. SELECT * FROM klanten K WHERE EXISTS (SELECT * FROM bestelde_artikelen BA WHERE K.klantnummer = BA.klantnummer) Wat hetzelfde is als de volgende query. SELECT DISTINCT K.* FROM klanten K, bestelde_artikelen BA WHERE K.klantnummer = BA.klantnummer

40

3.12sub-query's (vwo)

Het lijkt nu alsof IN en EXISTS feitelijk overbodig zijn. Bij eenvoudige query's die wij hier stellen is dat inderdaad het geval. Echter in grote databases zijn er situaties waarin je ze wel degelijk nodig hebt. Bovendien kan een sub-query in sommige gevallen veel sneller zijn dan een JOIN, zeker als we een database met tabellen met miljoenen records hebben. Bij sub-query's horen ook nog twee keywords te weten ALL en ANY. Soms kom je ook nog het keyword SOME tegen maar dat doet hetzelfde als ANY. Deze zijn handig om sommige ingewikkelde query's te vereenvoudigen. We kunnen ze namelijk gebruiken met vergelijkingsoperatoren bij sub-query's. We hebben op de vorige pagina een ingewikkelde query gezien die ons teruggeeft welke artikelen het minst verkocht zijn en hoe vaak. Wat we daarvoor deden was het minimum bepalen uit de tabel met daarin de som van alle hoeveelheden per artikel. We kunnen het laagste aantal ook anders bepalen. Als een getal namelijk lager dan of gelijk is aan alle waarden in een tabel dan hebben we ook het minimum gevonden. Dit kunnen we uitdrukken als <= ALL(tabel), waarbij we op de plaats van het woord tabel de query moeten zetten die de juiste tabel teruggeeft. Dat wil zeggen dat we SELECT artikel, SUM(hoeveelheid) AS S1 FROM bestelde_artikelen GROUP BY artikel HAVING S1 = (SELECT MIN(S2) FROM (SELECT artikel, SUM(hoeveelheid) AS S2 FROM bestelde_artikelen GROUP BY artikel) AS R) ook kunnen schrijven als SELECT artikel, SUM(hoeveelheid) AS S FROM bestelde_artikelen GROUP BY artikel HAVING S <= ALL(SELECT SUM(hoeveelheid) FROM bestelde_artikelen GROUP BY artikel) Zoals je ziet mogen we de sub-query tabel nu geen alias te geven. Omdat we nu direct in de sub-query tabel kijken mogen we daarin niet het veld artikel genereren. We kunnen immers geen hoeveelheden vergelijken met paren van artikelen en hoeveelheden. Ook de tweede alias S hebben we niet meer nodig. Opdracht 3-18 a. Maak een query die teruggeeft welke artikelen het meest verkocht zijn en hoe vaak. Maak een zo eenvoudig en efficiënt mogelijke query. b. Probeer zelf eens uit te zoeken wat ANY doet en opgave a op te lossen met behulp van ANY in plaats van ALL. c. Maak een query die een lijst van klanten (nummer en naam) teruggeeft die geen orders hebben geplaatst. Maak gebruik van ANY. d. Schrijf de query uit opdracht c op een andere manier. In de praktijk wordt ANY niet heel vaak gebruikt, maar ALL is wel een handig keyword. Hoofdstuk 3SQL

41