89 36

SQL: Structured Query Language

0

Inleiding

0.1 Informatiesystemen, gegevensbanken en tabellen Bij het automatiseren van gegevensbanken maakt men tegenwoordig bijna altijd gebruik van gegevensbank-beheerssystemen (database management systems, DBMS). Met zo'n systeem kan men eenvoudig gegevens in tabellen plaatsen, gegevens opzoeken of veranderen. Het maken van een tabel met behulp van een database management system is erg eenvoudig: men hoeft slechts één commando te geven (zoals wij later zullen zien). Wij gaan werken met relationele gegegevensbankbeheerssystemen (RDBMS). Relationele systemen werken met tabellen; je kunt tabellen maken, er gegevens in opslaan, met de gegevens in de tabellen 'rekenen', gegevens zoeken in tabellen. De tabel is dus het centrale begrip in een relationele database. Een gegevensbank in een relationeel gegevensbankbeheersysteem is niets anders dan een verzameling bij elkaar behorende tabellen. Zoals bijvoorbeeld in figuur 0.1, waar een gegevensbank met drie tabellen (spelers, boetes en teams) is weergegeven. Spelers

Spnr Spelernaam

Boetes Teams

Boetenr

Straatnaam

Plaatsnaam Teamnaam

Annastraat 24

Nijmegen

Heren

Nijmegen

Dames

1

K. Huisman

2

P.J. van Onderen Willemstraat 13

4

P. van Oosterom

Past. Pietstraat 14 Weurt

Heren

6

W. De Bom

Huilstraat 67

Heren

9

H. Flits

Zwanenveld 89-90 Nijmegen

Jeugd1

Kasteel 4

Wychen

Jeugd1

Nijmegen

Heren

Wychen

Spelersnr Bedrag 8 Datum F.G. Habank Vossendijk 288 Nijmegen L. Willemsen Aanvoerder 12/8/89 12 M.E.P. Graag

12

13 12/8/89 H. Nijboer

12Plein 44

5.00

Arnhem

Dames

13

10K. De Groot 19 13/8/89

34Huilstraat100.00 63

Wychen

Jeugd2

Jeugd2

34

20 13/8/89 20B.E. Melmans

28Kasteel 6 25.00

Wychen

Jeugd2

Dames

25

23

Huilstraat 67

Wychen

Jeugd1

Annastraat 24

Nijmegen

Jeugd1

Snelweg 673

Lent

Dames Jeugd2

Teamnaam

Klasse 1

2 Jeugd1

Heren

16

10

Dames

24 25

36W. De Bom 19/8/89

6

20/8/89 12

25

T. Huisman H. Fluit

25.00

Broerdijk 234

100.00 25.00

7

20/8/89 28 W.Jansen

42Wolvendijk 1.00 123

Elst

8

30 20/8/89 D. van Agt

12De Beurs 12 25.00

Groesbeek Jeugd1

9

32 2/9/89 K. Zwartjes

12Past. Pietstraat 5.002

Weurt

10

33 2/9/89 J. Mienen 34 3/9/89 H. Blaak

12Ekkersrijt 23 25.00

Eindhoven Heren

25Ekkersrijt 28 35.00

Eindhoven Jeugd2

11 14 15

36 37

D.F. Janssen

10/9/89

R. van der El

40 10/9/89 G.E. Noeg

Jeugd1

Groenstraat 45

Oss

Dames

Wolvendijk 28

Elst

Heren

28Eindeweg 14 5.00

Tiel

Jeugd2

34

5.00

42

R. Heymans

Dreef 24

Grave

Jeugd1

44

P. van Oss

Dreef 24

Oss

Jeugd1

figuur 0.1: een gegevensbank.

(C) Universitaire School voor Informatica

SQL. 1

KUNijmegen

SQL: Structured Query Language kolomnaam

Spelers

Spnr

Spelernaam

Straatnaam

Plaatsnaam

Teamnaam

1

K. Huisman

Annastraat 24

Nijmegen

Heren

2

P.J. van Onderen

Willemstraat 13

Nijmegen

Dames

4

P. van Oosterom

Past. Pietstraat 14

Weurt

Heren

6

W. De Bom

Huilstraat 67

Wychen

Heren

8

F.G. Habank

Vossendijk 288

Nijmegen

Dames

9

H. Flits

Zwanenveld 89-90

Nijmegen

Jeugd1

10

L. Willemsen

Kasteel 4

Wychen

Jeugd1

12

M.E.P. Graag

Broerdijk 234

Nijmegen

Heren

13

H. Nijboer

Plein 44

Arnhem

Dames

19

K. De Groot

Huilstraat 63

Wychen

Jeugd2

20

B.E. Melmans

Kasteel 6

Wychen

Jeugd2

23

W. De Bom

Huilstraat 67

Wychen

Jeugd1

24

T. Huisman

Annastraat 24

Nijmegen

Jeugd1

25

H. Fluit

Snelweg 673

Lent

Dames

28

W.Jansen

Wolvendijk 123

Elst

Jeugd2

30

D. van Agt

De Beurs 12

Groesbeek

Jeugd1

32

K. Zwartjes

Past. Pietstraat 2

Weurt

Jeugd1

33

J. Mienen

Ekkersrijt 23

Eindhoven

Heren

34

H. Blaak

Ekkersrijt 28

Eindhoven

Jeugd2

36

D.F. Janssen

Groenstraat 45

Oss

Dames

37

R. van der El

Wolvendijk 28

Elst

Heren

40

G.E. Noeg

Eindeweg 14

Tiel

Jeugd2

42

R. Heymans

Dreef 24

Grave

Jeugd1

44

P. van Oss

Dreef 24

Oss

Jeugd1

regel

Spnr = Spelersnr kolom

figuur 0.2: kolommen en regels. Gegevensbankbeheerssystemen beheren een gegevensbank; een gegevensbank bestaat uit tabellen en het systeem werkt dus op tabellen. Deze systemen zoeken in tabellen gegevens op, veranderen gegevens in regels, maken nieuwe regels met gegevens, etc. In deze cursus gebruiken wij een gegevensbankbeheersysteem waarbinnen de vraagtaal SQL beschikbaar is. SQL is de afkorting van Structured Query Language, hetgeen in het Nederlands zoiets betekent als ' gestructureerde gegevensvraagtaal' . De naam doet al vermoeden dat SQL een taal is voor het werken met gegevens. Een vraagtaal als SQL wordt dus vooral gebruikt om gegevens op te vragen. Daarnaast kent de taal elementen waarmee gegevens in de tabellen van de gegevenbank kunnen worden ingebracht en waarmee in de tabellen aanwezige gegevens kunnen worden gewijzigd. Wij zullen ons alleen richten op het opvragen van gegevens via SQL.


SQL. 2

KUNijmegen


0.2 Het voorbeeld informatiesysteem : de sportvereniging We zullen steeds een voorbeeldgegevensbank gebruiken. Deze gegevensbank heeft als naam de sportvereniging, voor de geautomatiseerde gegevensbank afgekort tot sportver. Deze gegevensbank bevat de gegevens uit de administratie van een sportvereniging. Deze zelfde sportvereniging komt ook terug in de syllabus bij het college (niet exact gelijk, maar wel bijna). De gegevensbank bevat 4 tabellen: de spelers -tabel, de teams -tabel, de boetes -tabel en de trainers -tabel. Hieronder staan beschrijvingen van de tabellen.

1. Spelers De tabel met de naam spelers bevat gegevens over de competitiespelers van de sportvereniging. De tabel heeft vijf kolommen: - een kolom met de naam spelersnr . Iedere speler heeft een spelersnummer en dit nummer staat vermeld in deze kolom. - een kolom met de naam spelernaam . In deze kolom staat de naam van de speler. De kolomnaam is een beetje krom, omdat de naam van de kolom maar een beperkte lengte mag hebben. - een kolom straatnaam . Deze kolom bevat de naam van de straat waar een speler woont en het huisnummer dat hij heeft. - een kolom plaatsnaam . De naam van de woonplaats van de speler staat in deze kolom. - een kolom teamnaam bevat de naam van het team, waarin de speler speelt. Spelers

Spnr

Spelernaam

Straatnaam

Plaatsnaam

Teamnaam

1

K. Huisman

Annastraat 24

Nijmegen

Heren

2

P.J. van Onderen

Willemstraat 13

Nijmegen

Dames

4

P. van Oosterom

Past. Pietstraat 14

Weurt

Heren

6

W. De Bom

Huilstraat 67

Wychen

Heren

8

F.G. Habank

Vossendijk 288

Nijmegen

Dames

9

H. Flits

Zwanenveld 89-90

Nijmegen

Jeugd1

10

L. Willemsen

Kasteel 4

Wychen

Jeugd1

12

M.E.P. Graag

Broerdijk 234

Nijmegen

Heren

13

H. Nijboer

Plein 44

Arnhem

Dames

19

K. De Groot

Huilstraat 63

Wychen

Jeugd2

20

B.E. Melmans

Kasteel 6

Wychen

Jeugd2

23

W. De Bom

Huilstraat 67

Wychen

Jeugd1

24

T. Huisman

Annastraat 24

Nijmegen

Jeugd1

25

H. Fluit

Snelweg 673

Lent

Dames

28

W.Jansen

Wolvendijk 123

Elst

Jeugd2

30

D. van Agt

De Beurs 12

Groesbeek

Jeugd1

32

K. Zwartjes

Past. Pietstraat 2

Weurt

Jeugd1

33

J. Mienen

Ekkersrijt 23

Eindhoven

Heren

34

H. Blaak

Ekkersrijt 28

Eindhoven

Jeugd2

36

D.F. Janssen

Groenstraat 45

Oss

Dames

37

R. van der El

Wolvendijk 28

Elst

Heren

40

G.E. Noeg

Eindeweg 14

Tiel

Jeugd2

42

R. Heymans

Dreef 24

Grave

Jeugd1

44

P. van Oss

Dreef 24

Oss

Jeugd1

Spnr = Spelersnr

figuur 0.3: De spelerstabel.


SQL. 3

KUNijmegen


2 Teams De teamstabel bevat gegevens over teams. In deze tabel vinden we drie kolommen: - een kolom met de naam teamnaam . Deze kolom bevat de namen van alle teams. In ons voorbeeld zijn er vier teams, namelijk ' Jeugd1' , ' Jeugd2' , ' Dames' en ' Heren' . - een kolom met de naam klasse . Deze kolom bevat de klasse waarin een team speelt. Er zijn vier klassen mogelijk, genummerd van 1 tot 4. - een kolom met de naam aanvoerder . Deze kolom bevat een aanduiding van wie de aanvoerder van het team is. Deze aanduiding is het spelersnummer van de aanvoerder.

Teams Teamnaam

Klasse

Aanvoerder

Jeugd1

1

10

Jeugd2

3

20

Dames

2

36

Heren

1

12

figuur 0.4: De teams tabel.

3 Boetes Deze tabel bevat de openstaande (niet betaalde) boetes. Boetes worden bepaald door de sportbond. Iedere opgelegde boete heeft een nummer, het zogeheten boetenummer. Voorts wordt bijgehouden wanneer het ' delict' gepleegd is en door wie, en tenslotte ook de hoogte van de boete. Boetes Boetenr

Datum

Spelersnr

Bedrag

1

12/8/89

12

25.00

2

12/8/89

12

5.00

3

13/8/89

34

100.00

4

13/8/89

28

25.00

5

19/8/89

6

100.00

6

20/8/89

25

25.00

7

20/8/89

42

1.00

8

20/8/89

12

25.00

9

2/9/89

12

5.00

10

2/9/89

12

25.00

11

3/9/89

25

35.00

14

10/9/89

34

5.00

15

10/9/89

28

5.00

figuur 0.5: De boetes tabel De betekenis van de kolommen: - een kolom boetenr . Deze kolom bevat het boetenummer. - een kolom datum . Deze kolom bevat de datum waarop de overtreding gepleegd is. - een kolom spelersnr . Het spelersnummer van de speler die de overtreding begaan heeft wordt in deze kolom vermeld (om de kosten te kunnen doorberekenen). - een kolom bedrag die het bedrag van de boete bevat.


SQL. 4

KUNijmegen


3 Trainers Een team kan verschillende trainers hebben, en een trainer kan verschillende teams trainen. In deze tabel staat welke teams welke trainers hebben. Een trainer van een team is altijd zelf een speler in de vereniging. - een kolom teamnaam bevat de naam van het team en - een kolom trainer bevat het spelersnummer van een trainer van het team. Trainers

Teamnaam

Spelersnr

Jeugd1

1

Jeugd1

2

Jeugd1

12

Jeugd2

1

Jeugd2

2

Dames

6

Heren

2

Heren

13

Heren

36

figuur 0.6 De trainerstabel


SQL. 5

KUNijmegen


1

Het opzetten van een gegevensbank

We hebben reeds gezien, dat een gegevensbank over het algemeen méérdere gegevenstabellen bevat. Afhankelijk van het gebruikte gegevensbankbeheerssysteem (DBMS) moet voor het opzetten van een nieuwe gegevensbank wel of niet eerst een ' lege' gegevensbank worden opgezet, vóórdat we in die ' lege' gegevensbank tabellen kunnen gaan definiëren. We richten ons nu op twee aspecten: 1) de in het oog te houden mogelijkheden met betrekking tot de zogenaamde gegevenstypen; 2) het creëren van de gegevenstabellen (al dan niet binnen een apart te definiëren gegevensbank).

1.1 Gegevenstypes Een computer is niet in staat alle mogelijke soorten, types van gegevens te verwerken. Het aantal types is beperkt. Aan de computer moet bekend zijn van welk type een gegeven is, voordat dit gegeven kan worden ingevoerd. Voor de computer ( of liever gezegd het database management systeem) is de wijze waarop een gegeven er uit ziet - het type van het gegeven - erg belangrijk. Een ander woord voor gegevenstype is datatype. Het aantal en het soort gegevenstypes dat we binnen een gegevensbank-beheerssysteem (DBMS) kunnen hanteren, hangt af van het te gebruiken (R)DBMS. Deze types zijn op zich vaker weer verder onder te verdelen in een aantal klassen. We zullen hieronder een aantal van die standaardtypes bespreken.

1. Type "geheel getal" a. Type: SMALLINT (SMALL INTeger), of in het Nederlands ' klein geheel getal' . Gegevens van dit type kunnen geen waarde voorstellen groter dan 32767 of kleiner dan -32767. De waarde van het voorgestelde getal moet dus liggen tussen -32767 en 32767. , of in het Nederlands ' geheel getal' . b. Type: INTEGER Gegevens van dit type kunnen een waarde voorstellen die moet liggen tussen -2147483647 en 2147483647. De waarde van een getal van het type ' INTEGER' moet dus ruwweg liggen tussen de -2 miljard en de +2 miljard.

2. Type "tekst" Willen wij een naam in de kolom van een tabel opslaan, dan kan dat natuurlijk niet als gegevens van het type geheel getal: namen zijn niet van het type getal, maar van het type tekst. Alle DBMS bieden de mogelijkheid om gegevens van het type tekst op te slaan. Je kunt het type tekst in SQL vergelijken met gegevenstypen zoals TEXT in Elan of STRING in (Turbo) Pascal. (variant: VARCHAR (lengte) ) Type: CHAR ( lengte ) CHARacter, of in het Nederlands ' kar akter' , ' teken' . Het SQL-RDBMS moet weten hoe lang de tekst maximaal mag zijn. Dit noemen we de lengte van een tekst. ' Jan' is een tekst met 3 tekens, ' Jan Klaassen' is een tekst met 12 tekens (de spatie achter Jan telt ook mee). Let op het gebruik van apostrofs om een tekst te ' begrenzen' . b.v. CHAR ( 10 ) (variant: VARCHAR (10) ) Een gegeven van dit gegevenstype is een tekst, die maximaal uit tien tekens mag bestaan.

3. Type "gebroken getal" Bijvoorbeeld: de inflatie in de maand oktober was 0.5 (procent), de BTW is 18.5 (procent), Janssen NV. heeft een omzet van 6.8 (miljoen gulden) etc. Het gegevenstype dat bij deze gegevens hoort heet in SQL ' DECIMAL' . Achter ' DECIMAL' moet je tussen haken aangeven hoe het gegeven er precies moetitzien. u Het eerste getal tussen haken geeft aan hoeveel cijfers er totaal in het gegeven staan. Een minteken telt hierbij ook mee. Het tweede getal tussen haken geeft aan hoeveel cijfers er achter de punt mogen staan. Type: DECIMAL ( x,y ) ' x' is daa rbij een getal dat het maximale aantal posities (minteken en/of cijfers) in het gegeven aangeeft, en ' y' is een getal dat aangeeft hoeveel posities (cijfers) er in het gegeven achter de punt maximaal mogen zijn. De diverse database-systemen hebben vaak nog andere gegevenstypen, zoals het datum-type en het Boolean-type (voor het opslaan van de waarheidswaarden ‘TRUE’ (= ‘waar’) of ‘FALSE’ ( ‘onwaar’) ).


SQL. 6

KUNijmegen


1.2 Het opzetten van tabellen Voordat wij gegevens in de tabellen (lades) van de gegevensbank kunnen invoeren moeten ook deze worden opgezet. In SQL kunnen wij dit doen door middel van het ' CREATE TABLE' commando. De taalregels waaraan dit commando moet voldoen ( de syntax, de grammatica van dit commando) vind je hierna in figuur 1.1.

NOT NULL tabelnaam

CREATE TABLE

(

kolomnaam

)

gegevenstype

, figuur 1.1: Syntax van het 'CREATE TABLE' commando. Een kolom kan slechts gegevens van eenzelfde gegevenstype bevatten; indien gebruik is gemaakt van de 'NOT NULL'optie, dan moet later in elk gegevensrecord het betreffende veld een waarde bevatten. Voorbeeld: Boetes Boetenr

Datum

Spelersnr

Bedrag

1

12/8/89

12

25.00

2

12/8/89

12

5.00

3

13/8/89

34

100.00

4

13/8/89

28

25.00

5

19/8/89

6

100.00

6

20/8/89

25

25.00

7

20/8/89

42

1.00

8

20/8/89

12

25.00

9

2/9/89

12

5.00

10

2/9/89

12

25.00

11

3/9/89

25

35.00

14

10/9/89

34

5.00

15

10/9/89

28

5.00

figuur 1.2: de boetes tabel. Indien in bovenstaande tabel gegevensrecords mogen voorkomen, waarin niet per se een waarde hoeft te zijn ingevuld voor het boete-bedrag (daar moet nog over beslist worden), dan kan hij worden opgezet door middel van het volgende commando: CREATE TABLE boetes ( boetenr SMALLINT datum CHAR(8) spelersnr SMALLINT bedrag DECIMAL ( 6, 2 )

NOT NULL , NOT NULL , NOT NULL , )

N.B. 1) Verschillende RDBMS hebben verschillende manieren om aan te geven, dat een SQL-commando op een volgende regel verder gaat. Hierboven zie je een voorbeeld waarbij na ieder deel van het commando een streepje '-' isoegevoegd, t behalve op de laatste regel. N.B. 2) Sommige RDBMS' s kennen aparte commando' s om een gebruikte database ' af te sluiten' .


SQL. 7

KUNijmegen


2. Het invoeren, verwijderen en veranderen van gegevens Wanneer je met gegevens in een gegevensbank werkt, kun je drie functies onderscheiden die nodig zijn: 1. Invoeren van gegevens (tabelregels). 2. Verwijderen van gegevens (tabelregels). 3. Veranderen van gegevens (tabelregels). We zullen in de volgende drie paragrafen op elk van deze drie functies afzonderlijk ingaan. Bedenk je steeds dat deze functies door het gegevensbank-beheerssysteem alleen kunnen worden uitgevoerd wanneer de gegevensbank opgezet en geopend is.

2.1 Het invoeren van gegevens Wanneer wij gegevens in een gegevensbank willen opslaan, dan doen we dit door een regel, waarin die gegevens staan, toe te voegen aan een tabel. Het toevoegen van een regel aan een tabel gebeurt door middel van het SQL commando ' INSERT' . Het syntax -diagram van dit commando staat in figuur 2.1. tabelnaam

INSERT INTO

(

kolomnaam

)

,

VALUES

(

waarde

)

,

figuur 2.1: syntax diagram van het ' INSERT' commando.

Voorbeeld: teams tabel gaat dan dus als volgt: Toevoegen van de regel ' Heren' , 1, 20 aan de INSERT INTO teams ( teamnaam, klasse, aanvoerder) VALUES ( ' Heren' 1, , 20 ) Bekijk nu figuur 2.1 nog eens en kijk of dit voorbeeld commando voldoet aan de regels die in het syntax-diagram vastliggen. Welke invulling hebben tabelnaam, kolomnaam en waarde?

N.B.

Uiteraard zullen bij zo' n INSERT -commando eventuele NOT NULL -eisen moeten worden gerespecteerd (zoniet, dan verschijnt er een foutmelding).


SQL. 8

KUNijmegen


2.1.2 Regels voor het INSERT-commando Zoals je uit dit syntaxdiagram kunt opmaken zijn er nogal wat mogelijkheden. Dit wordt geïllustreerd door de onderstaande voorbeelden. Voorbeeld: INSERT INTO teams ( aanvoerder, klasse, teamnaam ) VALUES ( 20, 1, ' Heren' ) is een correct commando. De volgorde verschilt weliswaar met die van de oorspronkelijke opzet van de tabel (' teamnaam' staat nu bijvoorbeelds al laatste genoemd), maar dat mag. Waar het om gaat is dat de volgorde van de in het commando aangegeven waarden in overeenstemming is met de in dit commando aangegeven kolomvolgorde. Voorbeeld: INSERT INTO teams ( teamnaam , klasse ) VALUES ( ' Heren' , 1 ) Dit heren-team heeft dus geen aanvoerder: de kolom met de naam aanvoerder wordt dan ook niet ingevuld.

2.2. Het verwijderen van gegevens Verwijderen van gegevens uit een gegevenstabel doe je met behulp van het DELETE-commando. De syntax van het commando ziet er als volgt uit: DELETE FROM tabelnaam

WHERE criterium

figuur 2.2: syntax-diagram van het DELETE-commando. criterium opnemen. Zoals je uit het syntax-diagram kunt lezen, kun je in het DELETE-commando een ' selectie' Voorbeeld:

DELETE FROM teams WHERE teamnaam = ' Heren'

Dit commando zorgt ervoor dat de laatste regel uit de tabel wordt verwijderd. Voorbeeld:

DELETE FROM teams WHERE klasse = 1

Uit de tabel worden alle regels waarin in de kolom klasse een 1 staat, verwijderd. Er worden, dus twee regels verwijderd: de regel waar in de kolom teamnaam ' Heren' staat, en de regel waar in de kolom teamnaam ' Jeugd1' staat. Het is ook mogelijk in het DELETE-commando géén criterium op te geven. Het commando ziet er dan uit als: DELETE FROM teams Het gevolg van dit commando is, dat alle regels uit de tabel worden verwijderd. Na dit commando zitten er dus geen regels meer in de tabel. Opletten geblazen dus met dit DELETE-commando!


SQL. 9

KUNijmegen


2.3. Het veranderen van gegevens. Met behulp van het UPDATE-commando kun je gegevens op een regel van een tabel wijzigen. Het syntaxdiagram van dit commando ziet er als volgt uit: UPDATE tabelnaam SET

kolomnaam

=

waarde

,

WHERE - criterium

figuur 2.3 : het syntaxdiagram van het UPDATE-commando.

We kunnen drie delen in het commando onderscheiden: - eerst moet je aangeven in welke tabel je gegevens wilt veranderen; de naam van deze tabel vul je achter ' UPDATE' in. - daarna geef je aan in welke kolommen gegevens veranderd moeten worden; deze kolommen (althans de kolomnamen) vul je in achter ' SET' ; achter iedere kolomnaam moet je ook nog aangeven van de nieuwe waarde in de kolom moet worden; deze waarde schrijf je achter het ' =' teken. - je kunt een selectiecriterium opnemen, net zoals in het DELETE-commando. Voorbeeld:

UPDATE SET WHERE

teams aanvoerder = 37 teamnaam = ' Heren'

Het resultaat is dat de aanvoerder van het heren-team wordt vervangen door een misschien wat minder overtredingen makend speler. N.B. 1. als je het selectie-criterium "WHERE teamnaam=' Heren' " achterwege zou laten, dan zouden plotselinge álle teams de speler met spelersnummer 37 als aanvoerder krijgen (een onmogelijke situatie!). N.B. 2. Uiteraard zullen ook bij een UPDATE-commando eventuele NOT NULL -eisen moeten worden gerespecteerd (zoniet, dan behoort er weer een foutmelding te verschijnen).


SQL. 10

KUNijmegen


3. Gegevensvragen op één tabel Er is slechts één commando in SQL, waarmee je gegevens kunt opvragen. Dit commando, het SELECT-commando, is erg uitgebreid, zo uitgebreid zelfs, dat we het volledige syntax diagram pas later zullen behandelen. We beginnen met wat eenvoudige gegevensvragen, die ook op een eenvoudige wijze met behulp van het SQL SELECT-commando te stellen zijn.

3.1 Selectie en projectie We kunnen twee soorten gegevensvragen onderscheiden: òf:

- wij vragen gegevens op uit bepaalde regels van een tabel, - wij vragen gegevens op uit bepaalde kolommen van een tabel.

Opvragen van bepaalde regels uit de tabel noemen wij selectie ; opvragen van bepaalde kolommen heet projectie . Beide soorten gegevensvragen worden met behulp van het SELECT-commando op de tabellen in de gegevensbank uitgevoerd.

3.1.1

Selectie

Met behulp van het SELECT-commando kunnen gegevens op regels uit een tabel geselecteerd worden (zie figuur 3.1). Willen wij niet alle regels van de tabel te zien krijgen, dan zullen wij moeten aangeven welke regels uit de tabel wij wèl willen zien (en welke niet). We moeten dan een selectiecriterium meegeven aan het SELECT-commando, een conditie waaraan de te selecteren regels uit de tabel moeten voldoen. Een selectie van regels uit een tabel ziet er in SQL altijd als volgt uit:

SELECT FROM WHERE

*

Natuurlijk moeten en nog worden ingevuld. Bij uitvoering van dit SQL-commando worden alle regels uit de tabel met de naam die je in hebt ingevuld, die aan voldoen, op het beeldscherm getoond. Het is belangrijk dat de ' *' achter ' SELECT' wordt intypt. Dit sterretje geeft aan gehele dat regels uit de tabel getoond moeten worden. We komen later, in de volgende paragraaf, nog op dit sterretje terug. Tabel

Geselecteerde regels

figuur 3.1 : selectie


SQL. 11

KUNijmegen


Voorbeeld: (Sportvereniging) De informatievraag: Welke spelers spelen er allemaal in het eerste jeugd-team? Het SQL-commando (de realisatie van de gegevensvraag): De informatie moet uit de tabel spelers komen, dus achter het woord ' FROM' komt deze tabelnaam. We willen alleen die spelers die in de teamnaam -kolom ' Jeugd1' hebben staan. Dus het = ' Jeugd1' . selectiecriterium achter ' WHERE' teamnaam is ' Het SQL-commando ziet er dan als volgt uit: SELECT FROM WHERE

* spelers teamnaam = ' Jeugd1'

Voorbeeld: (Sportvereniging) De informatievraag: Wat zijn de persoonsgegevens van de spelers die in Nijmegen wonen, en in het heren team spelen. Het SQL-commando (de realisatie van de gegevensvraag): De tabel is dus spelers en het selectiecriterium is teamnaam = ' Heren' AND plaatsnaam = ' Nijmegen' . SELECT FROM WHERE

* spelers teamnaam = ' Heren' AND plaatsnaam = ' Nijmegen'

In het selectiecriterium verbindt de 'AND' twee voorwaarden met elkaar: de betekenis van deze AND-verbinding is dat aan het selectiecriterium pas wordt voldaan als aan beide voorwaarden tegelijk voldaan is; de eerste voorwaarde èn (AND) de tweede voorwaarde moeten tegelijk geldig zijn. Alleen die regels worden geselecteerd, waarin de teamnaam gelijk is aan ' Heren'en tegelijkertijd de plaatsnaam gelijk is aan ' Nijmegen' . De regels in de tabel moeten dus aan beide voorwaarden tegelijkertijd voldoen. Een andere veelgebruikte verbinding tussen twee voorwaarden, wordt tot stand gebracht door: 'OR'. De betekenis van deze OR-verbinding is dat aan het selectiecriterium wordt voldaan als aan minstens één van de beide voorwaarden wordt voldaan. Het SQL-commando: SELECT FROM WHERE

* spelers teamnaam = ' Heren' OR plaatsnaam = ' Nijmegen'

levert dus alle regels uit de tabel spelers waarvan in de kolom teamnaam de waarde ' Heren' staat of (OR) waarvan in de kolom plaatsnaam de waarde ' Nijmegen' staat. De regels waarin zowel in de kolom teamnaam de waarde ' Heren' staat als in de kolom plaatsnaam de waarde ' Nijmegen' , horen hier dus ook bij. Behalve met de logische operatoren AND en OR kunnen ook combinaties met de NOT-operator (ontkenning!) worden gemaakt, zoals bijvoorbeeld in: SELECT FROM WHERE

* spelers ( NOT teamnaam = ' Heren' ) AND ( plaatsnaam = ' Nijmegen' )

N.B. het selectiecriterium NOT teamnaam=' Heren'

kan ook geformuleerd worden als: teamnaam<>' Heren' .

N.B. Let bij samengestelde criteria goed op het gebruik van haakjes. Zo geven de volgende criteria verschillende resultaten: WHERE ( NOT teamnaam = ' Heren' ) AND ( plaatsnaam = ' Nijmegen' OR plaatsnaam = ' Wychen' ) en: WHERE ( NOT teamnaam = ' Heren' ) AN D plaatsnaam = ' Nijmegen' OR plaatsnaam = ' Wychen'


SQL. 12

KUNijmegen


3.1.2

Projectie

Vaak zijn wij niet geïnteresseerd in alle gegevens die op een regel in een tabel staan, en willen wij alleen de gegevens uit bepaalde kolommen zien. In dit geval moeten wij op de tabel niet alleen een selectie (zie 3.1.1) uivoeren, maar ook een zogenaamde projectie. Bij een projectie worden uit de tabel alleen gegevens uit bepaalde kolommen geselecteerd. Figuur 3.5 geeft een illustratie van een pure projectie (alleen bepaalde kolommen geselecteerd, geen specifieke regels geselecteerd). Een projectie kan eveneens met het SQL-commando SELECT worden uitgevoerd; in dat geval selecteren wij bepaalde kolommen uit een tabel. Wanneer wij alléén kolommen selecteren (projectie) en niet ook nog regels selecteren (selectie) ziet het SELECT-commando er als volgt uit:

SELECT FROM

, ,

pure projectie op kolommen in een tabel Voor , , en moeten de namen van kolommen, respectievelijk tabellen ingevuld worden. Merk op dat het WHERE-gedeelte in het SQL-commando ontbreekt omdat slechts sprake is van een pure projectie. Tabel

Geselecteerde kolommen

figuur 3.5 : Projectie Bij uitvoering van dit commando worden alleen de gegevens uit de kolommen kolomnaam1, kolomnaam2 en kolomnaam3 getoond. Alleen díe gegevens worden getoond, die in deze kolommen in de tabel staan Natuurlijk is het mogelijk één kolomnaam achter SELECT te zetten, of twee, etc. Het afleiden van het benodigde SQL-commando uit de informatiebehoefte gaat op vergelijkbare wijze als in het geval van een selectie. Voorbeeld:

(Sportvereniging)

De informatievraag: De namen en adressen van de leden van de vereniging moeten worden getoond . Het SQL-commando (de realisatie van de gegevensvraag): Achter FROM moet de tabelnaam spelers komen te staan, en na SELECT de kolommen spelernaam , straatnaam en plaatsnaam (gescheiden door een komma). De SQL-vraag ziet er als volgt uit: SELECT FROM


spelernaam, straatnaam, plaatsnaam spelers

SQL. 13

KUNijmegen


3.1.3

Selectie en projectie

Natuurlijk kun je selectie en projectie met elkaar combineren. Je krijgt dan van bepaalde regels in de tabel, slechts bepaalde velden (kolommen) te zien (zie figuur 3.7). Tabel

Geselecteerde regels

Geselecteerde Getoonde gegevens

kolommen

figuur 3.7 : Selectie gecombineerd met projectie Meestal zul je, als je een gegevensvraag stelt, zowel selecteren als projecteren: je bent vaak alleen geïnteresseerd in de gegevens in bepaalde kolommen op bepaalde regels. Het syntax diagram van het vereenvoudigde SELECT-commando ziet er als volgt uit (zie hierna figuur 3.8): *

SELECT

kolomnaam ,

tabelnaam

FROM

WHERE

selectiecriterium

figuur 3.8 : Syntax diagram van het vereenvoudigde ' SELECT'-commando


SQL. 14

KUNijmegen


3.1.4.

Het systematisch opstellen van een SQL-query

Voor het systematisch opstellen van een SQL-query moeten tijdens de analyse van de informatievraag steeds de volgende zaken (in de genoemde volgorde) worden nagegaan: 1. Uit welke tabel komen de gegevens; 2. In welke kolommen staan de gegevens die nodig zijn (lees: die getoond moeten worden); 3. Wat is het selectiecriterium voor de regels? Voorbeeld: (Sportvereniging) De informatievraag: Wat zijn de namen en adressen van de speelster uit het dames-team. De analyse:

1) de gegevens moeten alle uit (alleen) de spelers-tabel komen; 2) gevraagd worden de gegevens uit de kolommen spelernaam, straatnaam en plaatsnaam; 3) we willen alleen gegevens uit die records waarbij in de ' teamnaam' -kolom ' Dames' staat.

Het SQL-commando (de realisatie van de gegevensvraag): SELECT spelernaam, straatnaam, plaatsnaam FROM spelers WHERE teamnaam = ' Dames'

3.1.5.

Rekenkundige operatoren: * , / , + en -

Zowel in het SELECT- als het WHERE-gedeelte van een SQL-query is (in de meeste SQL-systemen, maar bijvoorbeeld niet in WinSQL) het gebruik van rekenkundige operatoren mogelijk. Zo kun je bijvoorbeeld de boetebedragen boven de ƒ 50,- , verhoogd met 10% laten verschijnen via de query: SELECT 1.1 * bedrag FROM boetes (N.B. dit kan uiteraard gemakkelijker!) WHERE ( bedrag / 10 ) > 5


SQL. 15

KUNijmegen


3.2 SQL SELECT -Operatoren en -Functies We zijn niet altijd geïnteresseerd in precies díe gegevens die "letterlijk" in de gegevensbank zitten. Vaak willen wij van deze gegevens afgeleide informatie hebben. Bijvoorbeeld: een docent, die 130 tentamens gecorrigeerd heeft en de resultaten in een gegevensbank heeft ingevoerd, is - als deze resultaten eenmaal in de gegevensbank zitten - niet zozeer geïnteresseerd in de cijfers (want dat zijn er teveel om te kunnen overzien), maar wel in afgeleide informatie zoals het gemiddelde cijfer, het hoogste cijfer, het laagste cijfer, het aantal onvoldoendes etc. SQL biedt verschillende faciliteiten om uit gegevens in tabellen, dit soort afgeleide informatie te genereren. Deze faciliteiten kunnen benut worden door achter het woord SELECT in het SELECT-commando zogenaamde operatoren en/of functies toe te voegen, die bewerkingen op de gegevens uit de invoertabel tot gevolg hebben; het resultaat van deze bewerkingen wordt op het scherm zichtbaar gemaakt. We bespreken achtereenvolgens de volgende SQL-SELECT-operatoren en/of functies: DISTINCT => ALL COUNT( ...) SUM (...) MAX (...) MIN (...) AVG (...)

3.2.1.

=> alleen enkelvoudig tonen bij meervoudig vóórkomen ook meervoudig tonen (' default' ) => aantal voorkomens => som van waarden => maximale waarde => minimale waarde => AVeraGe (=gemiddelde)

De operator DISTINCT

Wij illustreren de werking van de operator DISTINCT aan de hand van een voorbeeld. Voorbeeld: (Sportvereniging) De informatievraag: Een lijst met spelersnummers van spelers die nog een boete hebben. Het SQL- commando (de realisatie van de gegevensvraag): Let op: als we het volgende commando zouden gebruiken: SELECT spelersnr FROM boetes dan zouden een aantal spelersnummers meervoudig in het overzicht verschijnen. We willen echter gewoon ' de sp elersnummers van beboete spelers' hebben. Daarom: dat kan wel via het volgende SQL-commando waarin de DISTINCT-operator is opgenomen: SELECT DISTINCT spelersnr FROM boetes Deze DISTINCT-operator ' zeeft' als het ware de duplicaten uitethresultaat van de uitvoering van het SQL-commando. De DISTINCT-operator kunnen we beschouwen als een soort deel-' commando' dat alle dubbele regels uit de resultaattabel zeeft, en een tabel oplevert zonder dubbele regels. Alle regels in de resultaattabel zijn dus verschillend. Figuur 3.14 illustreert dit. Spelersnr

Spelersnr

Spelersnr

12

6

6

12

12

12

34

12

25

28

12

28

6

12

34

25

12

42

42

25

12

25

12

28

12

28

25

34

34

34

28

42

oorspronkelijk

DISTINCT-operatie

resultaat

figuur 3.14: het effect van de DISTINCT operator. Merk op dat het resultaat van de DISTINCT-operator een geordende tabel oplevert.


SQL. 16

KUNijmegen


3.2.2.

De operator ALL

De operator ALL heeft precies de omgekeerde werking van de operator DISTINCT. Als het woord ALL achter SELECT in het SQL SELECT-commando staat, dan mogen er duplicaten in de antwoorden zitten. SQL gaat er vanuit dat je in iedere SQL-vraag ALL bedoelt, tenzij je DISTINCT gebruikt. In principe hoef je het woord ALL dus nìet te gebruiken.

3.2.3.

De functie COUNT

Een docent heeft 130 tentamens gecorrigeerd en de resultaten in een gegevensbank ingevoerd. Hij wil het aantal onvoldoendes weten. Door de COUNT-functie in het SELECT-commando op te nemen, kan het aantal regels in de uitkomst van een gegevensvraag geteld worden. De COUNT-functie telt het aantal voorkomens van hetgeen achter de functie tussen haakjes staat. Algemeen: SELECT COUNT ( < te tellen dingen > ) < tabelnaam > FROM [ WHERE < conditie > ] Even ter verduidelijking: datgene wat tussen vierkante haken (' [' ' ]' ) staat mag in het uiteindelijke commando staan, maar hoeft niet per se ingevuld te worden. Je mag in een SELECT-commando dus het selectie-criterium ( ) eventueel achterwege laten (daardoor wordt natuurlijk ook niet meer geselecteerd). Je moet voor iets in het uiteindelijke commando invullen. Datgene wat je invult wordt geteld. Dingen die je kunt invullen zijn: 1. Tel het aantal gehele regels; dit wordt aangegeven door een sterretje ' *' tussen de haakjes achter COUNT. 2. Tel de waarden in één kolom; er wordt een projectie uitgevoerd en het resultaat hiervan geteld. Meestal maakt het geen verschil of je een ' *' gebruikt of een kolomnaam. We komen hier later nog op terug. Voorbeeld: (Sportvereniging) De informatievraag: Hoeveel spelers zitten er in het eerste jeugdteam? Het SQL-commando (de realisatie van de gegevensvraag): De gegevens komen uit de spelers -tabel, het selectie-criterium is teamnaam = ' Jeugd1'. Deze selectie levert een aantal regels op. Dit aantal regels moeten wij laten tellen. Dit betekent dat wij COUNT moeten gebruiken. We zijn niet echt geïnteresseerd in het aantal spelersnummers of spelersnamen en dus hoeven wij niet te projecteren. We kunnen volstaan met de hele regels in de tabel te tellen. Dus: COUNT(*). SELECT COUNT(*) FROM spelers WHERE teamnaam = ' Jeugd1' Het effect op het scherm is:

COUNT(*) 8

Met COUNT(*) wordt het resultaat van de count aangegeven. Het telresultaat is 8. Voorbeeld: (Sportvereniging) De informatievraag: Hoeveel (verschillende) spelers hebben een boete? Hoe lossen we dit nu op? Het antwoord ligt min of meer voor de hand: wij maken gebruik van de operator DISTINCT. Het aantal verschillende spelersnr ' s moet namelijk geteld worden. Tussen de haakjes van COUNT zetten wij niet spelersnr , maar DISTINCT spelersnr. Het SQL-commando: Weer de boetes tabel, weer geen selectie, weer een projectie, maar nu alleen de verschillende spelersnr. SELECT FROM


COUNT ( DISTINCT spelersnr ) boetes

SQL. 17

KUNijmegen


3.2.4.

De functie SUM

Een ander handige functie is de SUM-functie. Deze telt waarden in een numerieke kolom bijelkaar op en laat het totaal (de som) op het beeldscherm zien. Voor de penningmeester is dit natuurlijke een ideale functie. Hij hoeft geen rekenmachine te gebruiken om de totale schuld van de vereniging aan de sportbond te weten te komen. Met behulp van SUM kun je alleen de waarden in numerieke (SMALLINT, INTEGER of DECIMAL) kolommen laten optellen. Voorbeeld: (Sportvereniging) De informatievraag: Wat is het totaal bedrag aan boetes? Het SQL-commando: Analoog aan vorige voorbeelden: de tabel is boetes , er is geen selectie-criterium, de projectiekolom is bedrag , deze is numeriek, dus we kunnen de SUM-functie hier op laten werken. SELECT FROM

SUM (bedrag) boetes

Je krijgt op je beeldscherm te zien: SUM (bedrag) 381.00

N.B. Let steeds goed op het verschil in betekenis en uiteraard in gebruik van enerzijds de functie COUNT(...) en anderzijds SUM (...). COUNT (...) staat voor het tellen van hoe vaak iets voorkomt en SUM (...) staat voor het optellen van (numerieke) gegevens, waardoor hun som bepaald wordt.

3.2.5.

De functies MAX (..), MIN (..) en AVG (..)

(AVeraGe = gemiddelde)

SQL biedt via een aantal ingebakken functies de mogelijkheid om de grootste (lees: maximale) waarde in een reeks (lees: kolom) te vinden, evenals de kleinste (lees: minimale) en de gemiddelde waarde. Daarvoor zijn respectievelijk de functies MAX (..), MIN (..) en AVG (..) beschikbaar. Je kunt de functies MIN(..) en MAX(..) gebruiken op kolommen waarin de waarden alfanumeriek zijn. Alfanumeriek wil zeggen, dat de kolommen gedefinieerd moeten zijn als getal òf als tekst (dus: SMALLINT, INTEGER, DECIMAL of CHAR). Indien je MAX (plaatsnaam ) opvraagt, dan komt er de ' alfabetisch' grootste plaatsnaam uit (' Wijchen' dus). Voor het bepalen van het gemiddelde van een reeks (lees: kolom) numerieke waarden, zou je uiteraard dat gemiddelde kunnen berekenen door alle te middelen waarden op te tellen (via de functie SUM(...) ) en de som ervan te delen door het aantal waarden (te bepalen via de functie COUNT(...) ) dat je hebt opgeteld. (Je zou dan daarna òf je rekenmachine kunnen gebruiken om dat gemiddelde te berekenen (door het gevonden aantal te delen op de som ) of [indien het SQL-systeem dat aankan] de delingsoperator ' /' gebruiken, zoals in bijvoorbeeld: SELECT SUM(bedrag) / COUNT(*) FROM boetes .) Om het je gemakkelijker te maken heeft men in SQL ook een aparte functie om het gemiddelde te berekenen. Deze average, dat is dus Engels voor gemiddelde). Uiteraard geldt hiervoor: deze functie AVG kan functieheet ' AVG' (van alleen op numerieke kolommen werken. Voorbeeld: (Sportvereniging) De informatievraag: Wat is de hoogste, de laagste en de gemiddelde boete die de speler met spelersnr 12 heeft gekregen? Het SQL-commando (realisatie van de gegevensvraag): Achter FROM komt boetes , achter WHERE komt spelersnr=12 , achter SELECT komt bedrag (dat houdt een projectie in!). Omdat wij (o.a.) het grootste bedrag willen hebben, moeten we de MAX-functie echter op bedrag toepassen. bedrag komt dus achter MAX tussen haakjes te staan. Datzelfde geldt voor de MIN- en de AVG-functie. SELECT FROM WHERE Op het scherm verschijnt:


MAX (bedrag ) , MIN (bedrag), AVG (bedrag) boetes spelersnr = 12 MIN (bedrag) 5.00

SQL. 18

MAX (bedrag) 25.00

AVG (bedrag) 17.00

KUNijmegen


3.3 SQL WHERE - Operatoren In tegenstelling tot de SQL SELECT operatoren, waarmee je aan geeft welke gegevens je precies wilt hebben (lees: selecteren), kun je met de WHERE operatoren aangeven aan welke voorwaarden deze gegevens moeten voldoen. We bespreken achtereenvolgens de (meeste van de) volgende SLQ-WHERE-operatoren: moet gelijk zijn aan . . . .. = .. moet ongelijk zijn aan ... (met hetzelfde effect als: NOT . . = . . ) . . <> . . moet kleiner, groter ..... zijn aan ... < , > , >= , <= komt voor in de verzameling waarden . . . IN . . . (inclusief grenzen) BETWEEN .. AND ... lijkend op ... (met wild cards ' _' en ' %' ) LIKE . . . test of een gegevensveld NULL (leeg; zonder waarde) is . . . IS NULL Bovendien kunnen we hier ' BOOLEAN'-operatoren gebruiken voor het combineren van voorwaarden en/of de ontkenning ervan: ontkenning van de erop volgende voorwaarde NOT ..... én de voorafgaande én de volgende voorwaarde ... AND ..... óf de voorafgaande én/of de volgende voorwaarde ... OR ....

3.3.1

De IN -operator

Eerder in dit hoofdstuk hebben we gezien dat condities, opgegeven bij het WHERE commando met elkaar gecombineerd kunnen worden door middel van de AND en OR operatoren. Willen we bijvoorbeeld alle spelers uit ' Nijmegen' , ' Arnhem' en ' Eindhoven' selecteren, dan zou dit kunnen met het volgende SQL commando: SELECT FROM WHERE OR OR

* spelers plaatsnaam = ' Nijmegen' plaatsnaam = ' Arnhem' plaatsnaam = ' Eindhoven'

Zoals je ziet is dit nogal omslachtig. Er is in SQL dan ook een operator waarmee we hetzelfde effect op een gemakkelijker manier kunnen bereiken. Bij de IN operator kunnen we een lijst van mogelijke waarden opgeven. Bovenstaand voorbeeld ziet er met de IN operator nu zo uit: SELECT FROM WHERE

* spelers plaatsnaam IN ( ' Nijmegen' , ' Arnhem' , ' Eindhoven' )

Het effect: Uitgangspunt is de spelerstabel zoals in figuur 3.2. Er is geen projectie dus alle gegevens worden getoond. Uitvoering van de IN operator levert een resultaat waarin alleen rijen voorkomen die de plaatsnamen ' Nijmegen' , ' Arnhem' of ' Eindhoven' bevatten.

3.3.2

De BETWEEN -operator

Ook met de BETWEEN -operator kunnen we een bepaalde conditie gemakkelijker opschrijven. Voorbeeld:

(Sportvereniging) De informatievraag: Geef alle gegevens van de spelers met een spelersnummer tussen de 10 en de 20. Het SQL-commando (de realisatie van de gegevensvraag): SELECT * FROM spelers WHERE spelersnr >= 10 AND spelersnr <= 20 Alternatief:


SELECT * FROM spelers WHERE spelersnr BETWEEN 10 AND 20

SQL. 19

KUNijmegen


3.3.3 De LIKE -operator. Het is vaak zo dat een gebruiker niet precies weet hoe een bepaalde naam gespeld wordt. De naam ' Jansen' bijvoorbeeld kan op verschillende manieren geschreven worden. Voor dit soort gevallen is binnen SQL een speciale operator aanwezig, de LIKE operator. Met deze LIKE operator kunnen we een patroon opgeven waaraan een bepaald gegeven moet voldoen. Omdat we niet precies weten hoe het gezochte woord gespeld wordt hebben we speciale tekens nodig waarmee we kunnen aangeven dat we het niet precies weten. Als we bijvoorbeeld opschrijven spelernaam LIKE ' Jan%' , dan bedoelen we daarmee dat de spelernaam moet beginnen met de letters ' Jan' gevolgd door een aantal (nul of meer) willekeurige tekens. Met een ander teken kunnen we aangeven dat we op een bepaalde plek precies één willekeurig teken willen zien. Het commando spelernaam LIKE ' Jan_' zorgt ervoor dat de spelernaam begint met de letters ' Jan' die worden gevolgd door precies één willekeurig teken. Voorbeeld:

(Sportvereniging) De informatievraag: Geef alle gegevens van de spelers waarvan de spelernaam het patroon ' Jansen' , ' Janssen' , ' Janse' of ' Jansse' bevat. Het SQL-commando (de realisatie van de gegevensvraag): SELECT * FROM spelers WHERE spelernaam LIKE ' %Jans%'

Opmerkingen: 1) De net besproken operatoren kunnen ook in combinatie met de NOT operator gebruikt worden. Enkele voorbeelden van selectiecriteria met behulp van die NOT-operator zijn: WHERE NOT spelersnr BETWEEN 10 AND 20 òf: WHERE spelersnr NOT BETWEEN 10 AND 20 òf: WHERE spelernaam NOT LIKE ' %Jans%' òf: WHERE NOT spelernaam LIKE ' %Jans%' òf: WHERE plaatsnaam NOT IN ( ' Nijmegen' , ' Arnhem' , ' Eindhoven' ) Boolean operatoren (AND ' Uiteraard' kunnen deze constructies gebruikt worden in combinatie met de andere en OR). Bijvoorbeeld: WHERE ( NOT spelernaam LIKE ' %Jans%' ) AND ( plaatsnaam = ' Nijmegen' ) 2) Bij de invulling van onze sportverenigings-database komen weliswaar geen NULL-waarden voor, maar gezien het CREATE TABLE boetes-commando (uit paragraaf 1.2) zou het volgende selectiecriterium bij een andere tabelinvulling bruikbaar kunnen zijn (al dan niet in OR/AND/etc.-combinatie met andere criteria): (of net: WHERE bedrag IS NOT NULL) WHERE bedrag IS NULL

3.4 Ordenen met SQL Als we een gegevensvraag omzetten naar een SQL commando krijgen we de resultaten in de volgorde waarin ze in de tabel zijn ingevoerd op het scherm. Er kunnen echter gegevensvragen voorkomen waarbij we een andere volgorde willen hebben. Hiervoor is in SQL het ORDER BY commando opgenomen. Bij dit ORDER BY commando kunnen we een kolomnaam als volgordecriterium opgeven waarbij we dan ook nog kunnen opgeven of de volgorde van hoog naar laag of van laag naar hoog moet lopen. De standaard volgorde die bij het ORDER BY commando wordt aangehouden is van laag naar hoog (ASCending). We behoeven dit dus ook niet op te geven. Willen we de ordening van hoog naar laag willen hebben, dan kunnen we dat aangeven met behulp van het commando DESC(ending). Voorbeeld: (Sportvereniging) Het probleem: De penningmeester wil de gegevens uit de boetetabel in volgorde van het spelersnr op het scherm zien. Het SQL-commando (de realisatie van de gegevensvraag): SELECT * FROM boetes ORDER BY spelersnr


SQL. 20

KUNijmegen


Indien in bovenstaand voorbeeld het sorteren net andersom (van hoog naar laag) had moeten plaatsvinden, dan je de optie DESC(ending) als volgt moeten meegeven: SELECT * FROM boetes ORDER BY spelersnr DESC

3.5 Gegevensvragen met groepering binnen één tabel. Vaak zijn we níet zo geïnteresseerd in gegevens zoals die letterlijk in een gegevenstabel zijn opgeslagen, maar wél in een overzicht van afgeleide gegevens per persoon of per team etc.. We spreken dan over het ' groeperen' van gegevens op een bepaald aspect. In deze paragraaf zullen we het hebben over gegevensvragen met betrekking tot groeperen binnen een bepaalde tabel. Groepering van gegevens die (zoals nog te behandelen in hoofdstuk 4) over meerdere tabellen zijn verspreid, zullen we in dit korte overzicht van SQL-mogelijkheden níet bespreken.

3.5.1

Groeperen : het GROUP BY - commando

Stel dat we het aantal spelers die in een bepaalde plaats wonen willen weten voor iedere plaats die voorkomt in de spelerstabel. Met de tot nu toe behandelde SQL-commando' s moeten we dan een fors aantal gegevensvragen opstellen (zie ook volgende bladzijde): SELECT FROM WHERE

COUNT(*) spelers plaatsnaam = ' Arnhem'

SELECT FROM WHERE

COUNT(*) spelers plaatsnaam = ' Eindhoven'

SELECT FROM WHERE

COUNT(*) spelers plaatsnaam = ' Elst'

SELECT FROM WHERE

COUNT(*) spelers plaatsnaam = ' Grave'

SELECT FROM WHERE

COUNT(*) spelers plaatsnaam = ' Groesbeek'

... en zo moeten we nog doorgaan met gelijksoortige vragen over Lent, Nijmegen, Oss, Tiel, Weurt en Wychen. Het is duidelijk dat dit nogal omslachtig is, en verder moeten we vooraf bepalen welke verschillende plaatsnamen er allemaal voorkomen in de tabel. Deze lijst van plaatsnamen zouden we eerst kunnen maken met behulp van het volgende SQL-commando: SELECT FROM

DISTINCT plaatsnaam spelers

Maar we zouden nog steeds een onbehoorlijk aantal SQL-commando' s moeten geven om voor iedere plaatsnaam uit te vinden hoeveel spelers er wonen. We zouden eigenlijk een commando willen hebben waarmee we in één keer een overzicht krijgen van het aantal spelers dat in elke, in de tabel voorkomende plaats, woont. We zullen zien dat dit kan met behulp van het GROUP BY commando. Het effect van dat GROUP BY commando is dat iedere berekening of functie in het SELECT gedeelte wordt toegepast op iedere individuele groep die we gespecificeerd hebben door middel van het GROUP BY commando.


SQL. 21

KUNijmegen


Ter voorbereiding zullen we eerst eens kijken naar het volgende SQL-commando: SELECT FROM ORDER BY

* spelers plaatsnaam

Het effect van dit SQL-commando kunnen we weergeven als in figuur 3.30 :

Spnr

Spelernaam

Straatnaam

13

H. Nijboer

33

groeps nummer

Plaatsnaam

Teamnaam

Plein 44

Arnhem

Dames

J. Mienen

Ekkersrijt 23

Eindhoven

Heren

34

H. Blaak

Ekkersrijt 28

Eindhoven

Jeugd2

28

W.Jansen

Wolvendijk 123

Elst

Jeugd2

37

R. van der El

Wolvendijk 28

Elst

Heren

42

R. Heymans

Dreef 24

Grave

Jeugd1

4

30

D. van Agt

De Beurs 12

Groesbeek

Jeugd1

5

25

H. Fluit

Snelweg 673

Lent

Dames

6

1

K. Huisman

Annastraat 24

Nijmegen

Heren

2

P.J. van Onderen

Willemstraat 13

Nijmegen

Dames

8

F.G. Habank

Vossendijk 288

Nijmegen

Dames

9

H. Flits

Zwanenveld 89-90

Nijmegen

Jeugd1

12

M.E.P. Graag

Broerdijk 234

Nijmegen

Heren

24

T. Huisman

Annastraat 24

Nijmegen

Jeugd1

36

D.F. Janssen

Groenstraat 45

Oss

Dames

44

P. van Oss

Dreef 24

Oss

Jeugd1

40

G.E. Noeg

Eindeweg 14

Tiel

Jeugd2

P. van Oosterom

Past. Pietstraat 14

Weurt

Heren

32

K. Zwartjes

Past. Pietstraat 2

Weurt

Jeugd1

6

W. De Bom

Huilstraat 67

Wychen

Heren

10

L. Willemsen

Kasteel 4

Wychen

Jeugd1

19

K. De Groot

Huilstraat 63

Wychen

Jeugd2

20

B.E. Melmans

Kasteel 6

Wychen

Jeugd2

23

W. De Bom

Huilstraat 67

Wychen

Jeugd1

4

1 2

3

7

8 9 10

11

Spnr = Spelersnr

figuur 3.30 : sortering van de spelerstabel op plaatsnaam als voorbereiding op ' groeperen' . We zien in de tabel van figuur 3.30 dat er in de spelerstabel élf groepen zijn aan te duiden, corresponderend met de verschillende plaatsnamen. Opmerking: De nummering van de groepen (aan de rechterkant van deze tabel 3.30) is in de figuur met de hand aangebracht en komt normaal gesproken niet op het beeldscherm.


SQL. 22

KUNijmegen


Als we binnen de gehele spelerstabel het aantal spelers willen tellen, dan doen we dat met het volgende SQLcommando: SELECT COUNT (*) FROM spelers De resultaattabel is weergegeven in figuur 3.31: COUNT(*) 24

figuur 3.31 : Het totale aantal spelers in de spelerstabel.

Het GROUP BY commando heeft op een normaal SQL commando het effect dat alle functies die in de SELECTregel voorkomen op iedere groep afzonderlijk worden toegepast. Als we nu het aantal spelers per plaatsnaam willen weten dan doen we dat met: SELECT plaatsnaam, COUNT (*) FROM spelers GROUP BY plaatsnaam Het effect van dit commando is weergegeven in figuur 3.32:

Plaatsnaam

COUNT(*)

Arnhem

1

Eindhoven

2

Elst

2

Grave

1

Groesbeek

1

Lent

1

Nijmegen

6

Oss

2

Tiel

1

Weurt

2

Wychen

5

figuur 3.32: Het aantal spelers (gegroepeerd) per plaatsnaam.

Opmerking: Alle kolomnamen die bij de SELECT voorkomen, moeten ook in de GROUP BY voorkomen. Verder mogen in die eerste SELECT regel nog functies voorkomen die werken op zo' n "groep" rijen uit de gegevenstabel (b.v. COUNT (*) of SUM (bedrag) etc.).


SQL. 23

KUNijmegen


Voorbeeld:

(Sportvereniging) Het probleem: De penningmeester van de sportvereniging stuurde eerst voor iedere boete een aparte rekening naar de betreffende speler. Uit het oogpunt van zuinigheid (portokosten) heeft hij nu besloten om voor iedere speler de verschillende boetes bij elkaar op te tellen en een gecombineerde rekening te sturen. Hierdoor is er meteen een beter overzicht over wat iedere speler nog moet betalen. De informatievraag: Geef de som van alle boetes per speler. De analyse van de informatievraag: De benodigde gegevens spelersnr en bedrag komen beide in de tabel boetes voor.

=====

Intermezzo We bekijken hier eerst de inhoud van de kolommen ' spelersnr' en ' bedrag' uit de tabel boetes, waarbij de tabelregels zijn geordend op ' spelersnr' , via het SQL commando: SELECT FROM ORDER BY

spelersnr, bedrag boetes spelersnr

Met als resultaat op het beeldscherm:

Spelersnr

Bedrag

6

100.00

12

25.00

12

5.00

12

25.00

12

5.00

12

25.00

25

25.00

25

35.00

28

25.00

28

5.00

34

100.00

34

5.00

42

1.00

figuur 3.33: Spelersnummers met boetebedragen, gesorteerd op spelersnummer

Uit het verkregen overzicht van figuur 3.33 valt gemakkelijk te zien, dat het mogelijk moet zijn om bijvoorbeeld per speler de totaal verschuldigde boete te laten bepalen. We zullen daarvoor moeten laten groeperen op spelersnr. Dat kan met behulp van het hierna volgende SQLcommando.

=====


Einde intermezzo

SQL. 24

KUNijmegen


Het SQL-commando: SELECT FROM GROUP BY

spelersnr, SUM (bedrag) boetes spelersnr

Geeft de volgende uitvoertabel:

spelersnr

SUM(bedrag)

6

100.00

12

85.00

25

60.00

28

30.00

34

105.00

42

1.00

figuur 3.34: Spelersnummers met bijbehorende totaalbedragen van de boete per speler.

3.5.2.

De HAVING - optie bij groeperen

Het kan ook voorkomen dat we de gegevens van een bepaalde gegevensvraag alleen dan willen krijgen als er aan een bepaalde groeperings(!)-voorwaarde is voldaan. Dit kan bewerkstelligd worden met behulp van het HAVING commando. Voorbeeld:

(Sportvereniging) Het probleem: We willen alleen die plaatsnamen met de bijbehorende spelersaantallen zien als er meer dan 4 spelers in de betreffende plaats wonen. Het SQL-commando: SELECT plaatsnaam, COUNT (*) FROM spelers GROUP BY plaatsnaam HAVING COUNT (*) > 4 Het effect: Plaatsnaam

COUNT(*)

Nijmegen

6

Wychen

5

figuur 3.35: resultaattabel van GROUP BY met HAVING-voorwaarde bij voorbeeld 3.20


SQL. 25

KUNijmegen


Voorbeeld:

(Sportvereniging) Het probleem: De penningmeester wil alleen het nummer en de totale boete zien van die spelers die méér dan 2 boetes hebben. Het SQL-commando: SELECT spelersnr, SUM (bedrag) FROM boetes GROUP BY spelersnr HAVING COUNT(*) > 2 Het effect: spelersnr

SUM(bedrag)

12

85.00

25

60.00

28

30.00

34

105.00

figuur 3.36: resultaattabel van GROUP BY met HAVING-voorwaarde bij voorbeeld 3.21

Opmerking: Houd steeds goed het verschil in de gaten tussen de soorten ' beperkingen' die je enerzijds kunt opleggen met het (eventuele) WHERE-deel van een SQL-query en anderzijds het (eventuele) HAVING-deel. WHERE betekent een (eerste) selectie (/beperking) op de afzonderlijke tabel-regels die in beschouwing moeten worden genomen, terwijl HAVING de groepjes (combinaties van tabelregels) die ' mee doen' uitselecteert. Ga zo voor jezelf na wat de betekenis (lees: de onderliggende informatievraag) is van de volgende query: SELECT teamnaam, COUNT (*) FROM spelers WHERE plaatsnaam = ' Nijmegen' GROUP BY teamnaam HAVING COUNT (*) > 1 en voorspel het door deze query te tonen resultaat.


SQL. 26

KUNijmegen


4. Gegevensvragen over meerdere tabellen. In een relationele gegevensbank zijn de gegevens meestal verspreid over meerdere tabellen opgeslagen. Gegevens die in verschillende tabellen zitten, kunnen heel wel een onderlinge relatie hebben (denk aan de term ' relationele gegevensbank' !); soms is die relatie tussengegevens in verschillende tabellen zelfs heel innig, maar zijn de gegevens om technische redenen niet in één tabel, maar in verschillende tabellen geplaatst . Tot nu toe hebben wij gegevensvragen bekeken, die steeds betrekking hadden op gegevens uit slechts één tabel. Voor het beantwoorden van veel interessante gegevensvragen moeten echter gegevens geraadpleegd worden die verspreid zijn over meerdere tabellen. In dit hoofdstuk behandelen wij enkele soorten gegevensvragen die gebruik maken van gegevens in meerdere tabellen. Het gaat om: 4.1. geneste gegevensvragen

( met ‘subqueries’ )

4.2. gegevensvragen met samenvoeging

( met ‘joins’ )

4.1 Geneste gegevensvragen

( met ‘subqueries’ )

Het woord ' geneste gegevensvragen' is eigenlijk een vertaling van het Engelse woord ' Nested Queries' . Een geneste gegevensvraag is een gegevensvraag, die zelf weer een andere gegevensvraag bevat ( voor het SELECT-commando betekent dit dat in het WHERE-deel weer een ander SELECT-commando staat). De geneste gegevensvraag is dus opgebouwd uit meerdere gegevensvragen; de meer naar binnen gelegen gegevensvragen heten ' subgegevensvragen' (Engels: ' Subqueries' ). Voorbeeld 4.1: (Sportvereniging) De informatievraag: Gevraagd wordt de naam van de aanvoerder van het heren-team. De analyse van de informatievraag (het ontwerp van de gegevensvraag): Het gaat om de naam van de aanvoerder van het herenteam. Als we naar de tabellen kijken dan zien wij dat wij het antwoord niet uit één tabel kunnen halen. De spelersnamen zitten in de spelers -tabel, de aanvoerder (althans zijn spelersnummer) moet uit de teams -tabel komen. We moeten een tweetraps gegevensvraag stellen: a. Geef uit de teams -tabel het spelersnummer van de speler die aanvoerder is van het heren-team. b. Geef uit de spelers -tabel de naam van de speler met het bij a. gevonden spelersnummer. Allereerst de eerste trap van de gegevensvraag: de informatie moet uit de teams -tabel komen. Deze gegevensvraag bevat zowel een projectie (we zijn alleen geïnteresseerd in het spelersnummer van de aanvoerder ) als een selectie (alleen de aanvoerder van het heren-team). Daarna de tweede trap van de gegevensvraag: dit gegeven moet uit de spelers -tabel komen. Ook deze gegevensvraag bestaat uit een projectie (op spelernaam ), en een selectie (alleen de naam van de aanvoerder). Hoe het selectie-criterium er precies uit moet zien is op dit moment nog niet duidelijk; wij vullen dat bij de realisatie pas in. Het SQL-commando ( de realisatie van de gegevensvraag): eerste trap

SELECT FROM WHERE

aanvoerder teams teamnaam = ' Heren'

tweede trap

SELECT FROM WHERE

spelernaam spelers speler is aanvoerder


SQL. 27

KUNijmegen


Natuurlijk moeten wij het selectie-criterium (speler is aanvoerder ) verder invullen. Uit de eerste trap van de gegevensvraag is bekend welk spelersnummer de aanvoerder heeft. Dit spelersnummer noemen we even spelersnr van aanvoerder . Als wij dit resultaat van de eerste trap van de gegevensvraag gebruiken in de tweede trap dan krijgen wij: tweede trap

SELECT FROM WHERE

spelernaam spelers spelersnr = spelersnr van aanvoerder

Nu hoeven we alleen nog spelersnr van aanvoerder om te zetten maar SQL. Maar dit spelersnr van aanvoerder is juist het resultaat van de eerste trap van de gegevensvraag. Wij vullen op de plaats van spelersnr van aanvoerder precies de eerste trap van de gegevensvraag gewoon in! Om die eerste trap van de gegevensvraag moeten dan echter wel haakjes ( ) komen te staan opdat duidelijk is wat bij elkaar hoort. Wij krijgen: SELECT FROM WHERE

spelernaam spelers spelersnr =

( SELECT FROM WHERE

aanvoerder teams teamnaam = ' Heren' )

Het effect: Uitvoering van het geneste SQL-commando begint met het binnenste sub-commando dat zonder meer is uit te voeren. De gegevens komen uit de teams -tabel. Het sub-commando houdt een selectie in (op teamnaam = ' Heren'). Het tussenresultaat wordt ook nog eens geprojecteerd (op aanvoerder ). Dit resultaat wordt ingevuld in het buitenste SQL-commando. Dit commando wordt dan: SELECT FROM WHERE

spelernaam spelers spelersnr = 12

Dit commando werkt op de spelers tabel. Het buitenste commando behelst weer een selectie en een projectie. Deze tussentabel moet nog geprojecteerd worden op spelernaam . Deze projectie levert dan als eindresultaat van het buitenste en dus ook van het gehele SQL-commando: Spelernaam

M.E.P. Graag

figuur 4.1 : het eindresultaat

Voorbeeld 4.2: (Sportvereniging) De informatievraag: Gevraagd worden de namen en adressen van díe spelers die een hogere boete moeten betalen dan f 25,00. Het SQL-commando: De eerste trap is net als in het vorige voorbeeld niet zo moeilijk op te schrijven. Het SQL-commando hiervoor is: eerste trap

SELECT spelersnr FROM boetes WHERE bedrag > 25.00

Het grote verschil met de eerste trap van voorbeeld 4.1 is het feit dat er met deze gegevensvraag een hele verzameling van spelersnummers opgeleverd wordt in tegenstelling tot het vorige voorbeeld waarbij maar één spelersnummer opgeleverd werd (een team heeft immers maar één aanvoerder). tweede trap

SELECT FROM WHERE


spelernaam, straatnaam, plaatsnaam spelers speler heeft hoge boete

SQL. 28

KUNijmegen


We gaan nu het resultaat van de eerste trap weer gebruiken om het selectiecriterium (speler heeft hoge boete ) verder in te vullen. Deze eerste trap levert in dit geval een aantal spelersnummers op, deze spelersnummers noemen we even spelersnrs van spelers met hoge boete. We kunnen nu dus géén gebruik maken van de ' =' operator omdat deze operator twee waarden met elkaar vergelijkt, en niet een waarde met een verzameling van waarden. We moeten hier dus een andere operator gebruiken namelijk de 'IN' operator. Als wij het resultaat van deeerste trap nu invullen in de tweede krijgen we: tweede trap

SELECT FROM WHERE

spelernaam, straatnaam, plaatsnaam spelers spelersnr IN spelersnrs van spelers met hoge boete

Nu hoeven we alleen de rest nog in te vullen net zoals in het vorige voorbeeld. We krijgen: SELECT FROM WHERE

spelernaam, straatnaam, plaatsnaam spelers spelersnr IN ( SELECT spelersnr FROM boetes WHERE bedrag > 25.00 )

Het eindresultaat is de tabel uit onderstaande figuur 4.2.

Spelernaam

Straatnaam

Plaatsnaam

W. De Bom

Huilstraat 67

Wychen

H. Fluit

Snelweg 673

Lent

H. Blaak

Ekkersrijt 28

Eindhoven

figuur 4.2: het eindresultaat

Algemene aandachtspunten bij het gebruik van subqueries : -

een subquery mag in zijn SELECT-clause slechts een enkele kolomnaam of expressie hebben; het resultaat van een subquery moet van een type zijn, dat vergelijkbaar is met het type waar het mee vergeleken wordt; binnen een subquery mag géén ORDER BY ... voorkomen. (Wel kun je uiteraard ter afsluiting van de totale query een ORDER BY-deel opnemen.)


SQL. 29

KUNijmegen


4.2 Gegevensvragen met samenvoeging (met ‘Join’ ) Join is het engelse woord voor ' samenvoeging' . In de vorige paragraaf is besproken hoe gegevensvragen die betrekking hebben op verschillende tabellen met elkaar kunnen worden gecombineerd in een geneste structuur. Een resultaatgegeven uit de ene tabel wordt gebruikt als ' stuurinformatie' voor de gegevensvraag op een andere tabel. Met geneste gegevensvragen is het alleen mogelijk gegevens uit één tabel zichtbaar te maken. Wanneer de informatievraag het noodzakelijk maakt gegevens uit meerdere tabellen zichtbaar te maken dan kunnen wij niet zonder meer een geneste gegevensvraag gebruiken. In zo' n geval moeten wij gebruik maken van samenvoegingen (joins) van gegevens in verschillende tabellen.

4.2.1. Samenvoeging - Join (algemeen) Een samenvoeging voegt gegevens uit twee of meer tabellen samen (op je beeldscherm of in tussentabellen). De wijze van samenvoeging is precies voorgeschreven: Indien je voor het samenvoegen geen enkele voorwaarde opgeeft, dan wordt elke regel uit de eerste samen te voegen tabel gecombineerd met iedere regel uit de tweede samen te voegen tabel (in geval van een samenvoeging van twee tabellen) tot één samengevoegde regel in de samengevoegde tabel. Hebben wij bijvoorbeeld een eerste tabel met 4 regels, en een tweede tabel met 5 regels, dan is het resultaat van de samenvoeging een tabel met 20 (4x5) regels. De eerste regel uit deze resultaattabel bestaat uit de eerste regel uit de eerste tabel, en de eerste regel uit de tweede tabel. De tweede regel uit de resultaattabel bestaat uit de eerste regel uit de eerste tabel, en de tweede regel uit de tweede tabel. De derde regel uit de resultaattabel bestaat uit de eerste regel uit de eerste tabel, en de derde regel uit de tweede tabel, ...

De naam van een kolom in de samenvoegingstabel bestaat altijd uit twee achter elkaar staande delen: - de naam van de tabel (waaruit de kolom oorspronkelijk afkomstig is) gevolgd door een punt, - de oorspronkelijke kolomnaam. Een dergelijke samenvoeging van twee totaal verschillende tabellen levert een resultaat dat over het algemeen niet zo veel zin zal hebben. Een betrekkelijk willekeurige combinatie van gegevens is het resultaat. Meestal worden samenvoegingen van tabellen gedaan als deze tabellen een kolom "gemeenschappelijk" hebben. Een voorbeeld van twee tabellen met een "gemeenschappelijke" kolom vind je in de sportvereniging: - de spelers -tabel (figuur 0.3) heeft de kolommen: Spelersnr, Spelersnaam, Straatnaam, Plaatsnaam en Teamnaam. - de boetes -tabel (figuur 0.5) heeft de kolommen: Boetenr, Datum, Spelersnr en Bedrag. De spelers -tabel en de boetes -tabel hebben de kolom Spelersnr "gemeenschappelijk". Je ziet deze "gemeenschappelijke" kolom dan in de samenvoegingstabel in twee gedaantes opduiken: als (d.w.z. de spelersnr-kolom uit de spelers-tabel) spelers.spelersnr en als (d.w.z. de spelersnr-kolom uit de boetes-tabel) boetes.spelersnr In het geval van een gemeenschappelijke kolom wordt samenvoeging van tabellen zinvol omdat via deze kolom een verband tussen de gegevens uit de ene tabel en de gegevens uit de andere tabel gelegd kan worden (N.B. Herinner je hier de term "Relationele Gegevensbank"). Zo levert in ons voorbeeld van de sportvereniging de samenvoeging van de spelers -tabel en de boetes -tabel als resultaat een tabel waarin van een speler die een boete heeft niet alleen zijn of haar spelersnummer staat, maar meteen ook zijn of haar naam en adres. Om na samenvoeging echter een "fatsoenlijke" resultaattabel over te houden moet je nog een selectie en een projectie uitvoeren. Kijk maar naar het voorbeeld van de samenvoeging van de spelers -tabel en de boetes -tabel.


SQL. 30

KUNijmegen


Voorbeeld: samenvoeging van de spelers -tabel en de boetes -tabel Het resultaat van samenvoeging is een tabel met 351 regels (27 regels van de spelers -tabel gecombineerd met 13 regels van de boetes -tabel) en 9 kolommen (5 kolommen uit de spelers -tabel gecombineerd met 4 kolommen uit de boetes -tabel). Hieronder staat een gedeelte van de samenvoegtabel weergegeven:

spelers

spelers.

spelers.

spelers.

spelernaam

straatnaam

plaatsnaam teamnaam

boetes.

boetes.

boetes.

boetes.

boetenr

datum

splrnr

bedrag

1

K. Huisman

Annastraat 24

Nijmegen

Heren

1

K. Huisman

Annastraat 24

Nijmegen

Heren

1

12/8/89

12

25.00

2

12/8/89

12

5.00

1

K. Huisman

Annastraat 24

Nijmegen

1

K. Huisman

Annastraat 24

Nijmegen

Heren

3

13/8/89

34

100.00

Heren

4

13/8/89

28

1

K. Huisman

Annastraat 24

25.00

Nijmegen

Heren

5

19/8/89

6

100.00

1

K. Huisman

Annastraat 24

Nijmegen

Heren

6

20/8/89

25

25.00

1 1

K. Huisman

Annastraat 24

Nijmegen

Heren

7

20/8/89

42

1.00

K. Huisman

Annastraat 24

Nijmegen

Heren

8

20/8/89

12

25.00

1

K. Huisman

Annastraat 24

Nijmegen

Heren

9

2/9/89

12

5.00

1

K. Huisman

Annastraat 24

Nijmegen

Heren

10

2/9/89

12

25.00

1

K. Huisman

Annastraat 24

Nijmegen

Heren

11

3/9/89

25

35.00

1

K. Huisman

Annastraat 24

Nijmegen

Heren

14

10/9/89

34

5.00

1

K. Huisman

Annastraat 24

Nijmegen

Heren

15

10/9/89

28

5.00

2

P.J. van Onderen

Willemstraat 13

Nijmegen

Dames

1

12/8/89

12

25.00

2

P.J. van Onderen

Willemstraat 13

Nijmegen

Dames

2

12/8/89

12

5.00

2

P.J. van Onderen

Willemstraat 13

Nijmegen

Dames

3

13/8/89

34

100.00

…

…

…

splnr

…

…

spelers.

…

…

…

…

44

P. van Oss

Dreef 24

Oss

Jeugd1

11

3/9/89

25

35.00

44

P. van Oss

Dreef 24

Oss

Jeugd1

14

10/9/89

34

5.00

44

P. van Oss

Dreef 24

Oss

Jeugd1

15

10/9/89

28

5.00

figuur 4.3 : het resultaat van samenvoeging van spelers -tabel en boetes -tabel. In dit resultaat zou je inderdaad terug kunnen vinden dat de speler met spelersnummer 12 een boete heeft van f 25,00 en dat deze speler M.E.P. Graag is, wonende Broerdijk 234 in Nijmegen, maar (afgezien van het feit dat in de figuur hierboven M.E.P. Graag niet zichtbaar is) je zou in het resultaat van de samenvoeging aardig moeten zoeken. Verder zie je dat in de resultaattabel zinloze regels voorkomen; neem de eerste 13 regels maar: daar staat een zinloze combinatie van gegevens. De gegevens die uit de spelers -tabel afkomstig zijn, vormen samen een logische combinatie en de gegevens die uit de boetes -tabel afkomstig zijn, vormen ook samen een logische combinatie. De totale combinatie van die gegevens is echter niet zinvol, althans niet voor bijvoorbeeld de speler met spelersnummer 1. Conclusie is dat slechts bepaalde delen van de samenvoegingstabel interessant zijn. Die delen moeten door selectie in een resultaattabel worden samengebracht.


SQL. 31

KUNijmegen


Samenvoegcriterium Bij het selecteren van de interessante delen uit de samenvoegingstabel speelt de "gemeenschappelijke" kolom een belangrijke rol. In ons voorbeeld hiervoor is dit de kolom Spelersnr, die in de samenvoegingstabel als spelers.spelersnr en als boetes.spelersnr voorkomt. Om te komen tot zinvolle samenvoeging is een samenvoegcriterium noodzakelijk. Dit samenvoegcriterium is een selectiecriterium waarin een gemeenschappelijke kolom een rol speelt. In ons voorbeeld is spelers.spelersnr = boetes.spelersnr het samenvoegcriterium met als gemeenschappelijke kolom: spelersnr. Laten wij de regels met zinvolle combinaties van gegevens die met het samenvoegcriterium spelers.spelersnr = boetes.spelersnr geselecteerd worden eens samenbrengen in een tussentabel. Deze tabel staat in figuur 4.4. spelers spelers.

spelers.

splnr spelernaam straatnaam 6

spelers.

spelers. boetes. boetes.

plaatsnaam teamnaam boetenr datum

boetes. boetes. splnr

bedrag

W. De Bom

Huilstraat 67

Wychen

Heren

5

19/8/89

6

100.00

12

M.E.P. Graag

Broerdijk 234

Nijmegen Heren

1

12/8/89

12

25.00

12

M.E.P. Graag

Broerdijk 234

Nijmegen Heren

2

12/8/89

12

5.00

12

M.E.P. Graag

Broerdijk 234

Nijmegen Heren

8

20/8/89

12

25.00

12

M.E.P. Graag

Broerdijk 234

Nijmegen Heren

9

2/9/89

12

5.00

12

M.E.P. Graag

Broerdijk 234

Nijmegen Heren

10

2/9/89

12

25.00

25

H. Fluit

Snelweg 673

Lent

Dames

6

20/8/89

25

25.00

25

H. Fluit

Snelweg 673

Lent

Dames

11

3/9/89

25

35.00

28

W.Jansen

Wolvendijk 123 Elst

Jeugd2

4

13/8/89

28

25.00

28

W.Jansen

Wolvendijk 123 Elst

Jeugd2

15

10/9/89

28

5.00

34

H. Blaak

Ekkersrijt 28

EindhovenJeugd2

3

13/8/89

34

100.00

34

H. Blaak

Ekkersrijt 28

EindhovenJeugd2

14

10/9/89

34

5.00

42

R. Heymans

Dreef 24

Grave

7

20/8/89

42

1.00

Jeugd1

Spnr = Spelersnr

figuur 4.4 : resultaat van selectie op spelersnummers die zowel in de spelers -tabel als in de boetes -tabel voorkomen. Zeer waarschijnlijk zal de tabel nog steeds te veel gegevens bevatten. Stel dat wij alleen de naam, het adres en het boetebedrag zouden willen weten. In dat geval zouden wij het tussenresultaat uit figuur 4.4 moeten projecteren op de kolommen: spelers.spelersnaam, spelers.straatnaam, spelers.plaatsnaam en boetes.bedrag. Het resultaat van deze projectie staat in figuur 4.5.

figuur 4.5 :

spelers.

spelers.

spelers.

boetes.

spelernaam

straatnaam

plaatsnaam

bedrag 100.00

W. De Bom

Huilstraat 67

Wychen

M.E.P. Graag

Broerdijk 234

Nijmegen

M.E.P. Graag

Broerdijk 234

Nijmegen

5.00

M.E.P. Graag

Broerdijk 234

Nijmegen

25.00

M.E.P. Graag

Broerdijk 234

Nijmegen

5.00

M.E.P. Graag

Broerdijk 234

Nijmegen

25.00

H. Fluit

Snelweg 673

Lent

25.00

H. Fluit

Snelweg 673

Lent

35.00

W.Jansen

Wolvendijk 123

Elst

25.00

W.Jansen

Wolvendijk 123

Elst

H. Blaak

Ekkersrijt 28

Eindhoven

100.00

H. Blaak

Ekkersrijt 28

Eindhoven

5.00

R. Heymans

Dreef 24

Grave

1.00

25.00

5.00

Eindresultaat na selectie op identiek spelersnummer en projectie op naam, adres (straatnaam en plaatsnaam) en boetebedrag.


SQL. 32

KUNijmegen


4.2.2. Samenvoeging in SQL : het samenvoegen van tabellen Hiervóór hebben wij samenvoeging van tabellen in het algemeen bekeken. Hoe gaat deze samenvoeging nu in SQL? Samenvoeging wordt gedaan met behulp van het SELECT commando. Wij geven aan dat tabellen samengevoegd moet worden, door achter FROM de tabelnamen (gescheiden door komma' s) neer te schrijven. Natuurlijk kan in principe een willekeurig aantal tabellen worden samengevoegd; in plaats van twéé tabelnamen kunnen wij dus ook dríe tabelnamen achter FROM zetten; in dat geval worden de gegevens van drie tabellen samengevoegd. Voorbeeld 4.3: (Sportvereniging) De informatievraag: Geef de namen, adressen en hoogte van de boete (bedrag) van alle spelers die een boete hebben. Het SQL-commando (de realisatie van de gegevensvraag): We werken met twee tabellen tegelijk: spelers en boetes . Achter het woord FROM komen dus deze twee tabelnamen te staan: FROM spelers, boetes . We moeten ook projecteren: alleen spelernaam , straatnaam, plaatsnaam en bedrag moeten in het resultaat voorkomen. Voor iedere kolom in de samenvoegingstabel moeten we aangeven uit welke tabel deze oorspronkelijk komt. Beide tabellen worden samengevoegd op grond van een zinnig samenvoegcriterium. De ' verbinding' tussen de tabellen wordt gevormd door de gemeenschappelijke (=in beide tabellen voorkomende) kolom spelersnr . We eisen nu dat het spelersnummer in beide kolommen gelijk moet zijn: spelers.spelersnr = boetes.spelersnr. SELECT FROM WHERE

spelers.spelernaam, spelers.straatnaam, spelers.plaatsnaam, boetes.bedrag spelers, boetes spelers.spelersnr = boetes.spelersnr

Het resultaat van dit SELECT-commando staat in de reeds getoonde figuur 4.5. Syntaxdiagram SELECT-commando voor samenvoeging : SELECT

tabelnaam

.

kolomnaam

,

FROM

tabelnaam ,

WHERE

samenvoegcriterium AND

selectiecriterium

figuur 4.6: syntax-diagram van SELECT voor samenvoeging

Arbeidsbesparing door o.a. het gebruik van aliassen We kunnen de hoeveelheid werk bij het intikken van een SQL-join-query vaak sterk verminderen door gebruik te maken van de volgende twee aspecten: indien een bepaalde kolomnaam slechts in één van de samengevoegde gegevenstabellen voorkomt, dan hoeven we niet expliciet naar die tabel te refereren (zo komt b.v. de kolomnaam spelernaam alleen in de spelers-tabel voor); we kunnen in de FROM-regel direct achter een genoemde tabelnaam (daarvan gescheiden door een spatie) een te gebruiken afkorting (een ' alias' ) voor die tabelnaam geven . Vaak gebruiken we de eerste letter van een tabelnaam voor zo' n alias -naam (waaraan zonodig een cijfer ter nummering wordt toegevoegd). Toepassing van deze arbeidsbesparende mogelijkheden staat ons toe de voorgaande query te formuleren als: SELECT spelernaam, straatnaam, plaatsnaam, bedrag FROM spelers s , boetes b WHERE s.spelersnr = b.spelersnr N.B.

FoxPro (v 2.6) bij wat moeilijkere Niet alle SQL-systemen kunnen goed overweg met ' aliassen' . Zo heeft b.v. queries er moeite mee en geeft dan òf een foutmelding òf toont een verkeerd eindresultaat.


SQL. 33

KUNijmegen


4.2.3 Joins versus subqueries Vaak kan een informatie-vraag zowel via een join- als via een subquery-formulering beantwoord worden. Je zult echter joins moeten gebruiken: indien je gegevens uit verschillende tabellen in één keer op (eenzelfde regel van) het scherm wilt hebben. Je zult subqueries moeten gebruiken: - indien je (zie verderop bij paragraaf 4.3.3) met minimum- of maximumwaarden te maken hebt; - in het algemeen bij (via functies) afgeleide condities.

4.3 Gegevensvragen met groepering (deel 2) In de vorige paragrafen is besproken hoe gegevensvragen die betrekking hebben op meerdere tabellen met elkaar gecombineerd kunnen worden tot een geneste of tot een samengevoegde structuur. In hoofdstuk 3 (paragraaf 3.5) is het groeperen van gegevens binnen één tabel besproken. In deze paragraaf gaan we het hebben over gegevensvragen die betrekking hebben op het groeperen van gegevens die over meerdere tabellen verspreid staan.

4.3.1 Groeperen over meerdere tabellen. Het concept van groeperen kan uiteraard ook verwerkt zitten in complexere gegevensvragen. We bespreken nu eerst een voorbeeld waarbij ' groeperen' gecombineerd wordt met ' samenvoegen' . Voorbeeld 4.5: (Sportvereniging) De informatievraag: Geef de som van alle boetes per speler met de bijbehorende spelernamen. Het SQL-commando: We splitsen het tot stand komen van het gezochte SQL-commando op in twee stappen: Stap 1:

de samenvoeging van de tabellen spelers en boetes via de SQL-query: SELECT FROM WHERE

spelers.spelernaam, boetes.bedrag spelers , boetes spelers.spelersnr = boetes.spelersnr

geeft de volgende tussentabel (zie eventueel ook voorbeeld 4.3): spelers.

boetes.

spelernaam

bedrag

W. De Bom

100.00

M.E.P. Graag

25.00

M.E.P. Graag

5.00

M.E.P. Graag

25.00

M.E.P. Graag

5.00

M.E.P. Graag

25.00

H. Fluit

25.00

H. Fluit

35.00

W.Jansen

25.00

W.Jansen

5.00

H. Blaak

100.00

H. Blaak

5.00

R. Heymans

1.00

figuur 4.8: tussentabel: de samenvoegingstabel na selectie op samenvoegcriterium.


SQL. 34

KUNijmegen


Stap 2:

op de resultaten van deze tussentabel gaan we nu de groepering op spelernaam uitvoeren. Via een combinatie van de SQL-opdracht uit stap 1 met de groeperingsopdracht GROUP BY spelers.spelernaam en het optellen van de bedragen, kunnen we de gewenste eindresultaattabel bereiken.

De totale SQL-gegevensvraag (met toepassing van aliassen en andere tikwerkbesparende maatregelen) wordt hierna dus: SELECT spelernaam, SUM (bedrag) FROM spelers S, boetes B WHERE S.spelersnr = B.spelersnr GROUP BY spelernaam en die geeft de volgende uitvoertabel: spelers. spelernaam H. Blaak

SUM(boetes. bedrag) 105.00

H. Fluit

60.00

M.E.P. Graag

85.00

R. Heymans

1.00

W. De Bom

100.00

W. Jansen

30.00

figuur 4.9: de eindresultaattabel: spelersnamen met bijbehorende boetebedragen.

Let op: reeds eerder is opgemerkt, dat álle kolomnamen die in een SELECT-regel voorkomen óf in de GROUP BY-regel moeten voorkomen, óf in die SELECT-regel in een functie-vorm (met SUM, MIN, etc. over een groep rijen) moeten voorkomen. Níet mogelijk is dus een SQL-query als: SELECT FROM WHERE GROUP BY

spelernaam, plaatsnaam, SUM (bedrag) spelers S, boetes B S.spelersnr = B.spelersnr spelernaam

FOUT !!

Immers: de in de SELECT-regel voorkomende ' plaatsnaam' komt níet in de groeperingsregel terug en ook niet in een vorm met een functie. Indien we in een overzicht tóch die plaatsnaam willen zien, dan zijn daarvoor twee mogelijkheden: 1º. we nemen die plaatsnaam in de SELECT-regel in een functie-vorm op: SELECT FROM WHERE GROUP BY

spelernaam, MAX (plaatsnaam), SUM (bedrag) spelers S, boetes B S.spelersnr = B.spelersnr spelernaam

GOED !!

(omdat de bij eenzelfde speler voorkomende plaatsnamen alle hetzelfde zijn, zullen ook de functies MAX(plaatsnaam) of MIN(plaatsnaam) diezelfde waarde opleveren; immers de maximale of minimale waarde van ' Arnhem' , ' Arnhem' en ' Arnhem' is eveneens ' Arnhem' !) 2º. we nemen die plaatsnaam (naast de spelernaam) bij het groeperingsproces op: SELECT FROM WHERE GROUP BY

spelernaam, plaatsnaam, SUM (bedrag) spelers S, boetes B S.spelersnr = B.spelersnr spelernaam, plaatsnaam

GOED !!

(omdat bij eenzelfde speler voorkomende plaatsnamen alle hetzelfde zijn, zal groeperen op combinatie van spelernaam+plaatsnaam hetzelfde resultaat opleveren als alléén groeperen op spelernaam!) Beide genoemde mogelijkheden zijn beslist ' tricky'te noemen, maar toepassing van een van hen is wel noodzakelijk als we een overzicht willen hebben, waarin behalve de spelernaam en de bijbehorende totaalboete ook de naam van de woonplaats moet zijn opgenomen!


SQL. 35

KUNijmegen


4.3.2 Het HAVING -commando bij ' groeperen over méérdere tabellen' Net als bij groeperen binnen één tabel (zie hoofdstuk 3, subparagraaf 3.5.2) kunnen we ook bij het groeperen van gegevens over meerdere tabellen de ' HAVING'-optie gebruiken om groepscriteria aan te geven. We geven nu een voorbeeld met ' groeperen over meerdere tabellen' , waarbij we via de HAVING -optie ervoor zorgen, dat van alleen die groepen die aan bepaalde voorwaarden voldoen, de gevraagde afgeleide gegevens getoond worden. Voorbeeld 4.5: (Sportvereniging) De informatievraag: Geef het aantal en de som van alle boetes per team met de bijbehorende teamnaam voor die teams, die meer dan 3 boetes of die een totaalboete van meer dan ƒ 50 hebben. Het SQL-commando: We splitsen het tot stand komen van het gezochte SQL-commando op in twee stappen: Stap 1: de geschetste samenvoeging van de tabellen spelers en boetes via de SQL-query (met voor een beter overzicht tevens een ordening op teamnaam): SELECT FROM WHERE ORDER BY

spelers.teamnaam, boetes.bedrag spelers , boetes spelers.spelersnr = boetes.spelersnr spelers.teamnaam

geeft de volgende tussentabel: spelers.

boetes.

teamnaam

bedrag

Dames

25.00

Dames

35.00

Heren

5.00

Heren

5.00

Heren

25.00

Heren

25.00

Heren

25.00

Heren

100.00

Jeugd1

1.00

Jeugd2

5.00

Jeugd2

5.00

Jeugd2

25.00

Jeugd2

100.00

figuur 4.10: tussentabel: de samenvoegingstabel na selectie op samenvoegcriterium en ordening Stap 2: op de resultaten van deze tussentabel gaan we nu de groepering op teamnaam uitvoeren. Via een combinatie van de SQL-opdracht uit stap 1 met de groeperingsopdracht GROUP BY spelers.teamnaam en het tellen en optellen van de bedragen en tot slot het samengestelde groepscriterium HAVING COUNT(*)>3 OR SUM(boetes.bedrag)>50 kunnen we de gewenste eindresultaattabel bereiken. De totale SQL-gegevensvraag (met gebruik van de bekende arbeidsbesparende maatregelen) wordt dan: SELECT teamnaam, COUNT (*), SUM (bedrag) FROM spelers s, boetes b WHERE s.spelersnr = b.spelersnr GROUP BY teamnaam HAVING COUNT (*) > 3 OR SUM (bedrag) > 50


SQL. 36

KUNijmegen


en die geeft de volgende uitvoertabel: spelers.

SUM(boetes.

COUNT(*)

teamnaam

bedrag)

Dames

2

60.00

Heren

6

185.00

Jeugd2

4

135.00

figuur 4.11: de eindresultaattabel: geselecteerde teamnamen met bijbehorende boetegegevens.

N.B.

Als je het resultaat van voorgaande SQL-query geordend naar opklimmend aantal boetes zou willen laten verschijnen, dan kan dat door op het einde van die query toe te voegen: ORDER BY 2 . Omdat we in een ORDER BY-regel géén functies of operatoren (zoals COUNT(*) e.d.) mogen gebruiken, zijn we aangewezen op de in hoofdstuk 3 genoemde mogelijkheid om in die ORDER BY-regel het nummer van de kolom uit de resultatentabel te gebruiken.

4.3.3 Extreme totaalwaardes bij groeperen In hoofdstuk 3 paragraaf 3.2) heb je kennisgemaakt met de functies MIN(..) en MAX(..). Met behulp van deze functies kun je respectievelijk de kleinste en de grootste voorkomende waarde in een kolom van een gegevenstabel bepalen. Voorbeelden daarvan zijn ' SELECT MIN(bedrag) FROMboetes ...' en ' SELECT MAX(plaatsnaam) FROM spelers ...' . Tussen de haakjes achter MIN of MAX moet steeds eenkolomnaam worden ingevuld. In de praktijk komen vaak gegevensvragen voor, waarbij je geïnteresseerd bent in een minimum- of maximumwaarde ná groeperen. Zo zou je je de vraag kunnen stellen, welke speler(snummer) de hoogste totaalboete heeft. Omdat (zoals hierboven gesteld) je die totaalboete niet kunt bepalen via SELECT (MAX(SUM(bedrag)) ... moeten we voor die bepaling een andere constructie gebruiken. We zullen zien dat hiervoor gebruik gemaakt kan worden van een constructie met ' >= ALL' (voor maxima) of ' <= ALL' (in geval van minima). Voorbeeld 4.6: (Sportvereniging) De informatievraag: Geef het spelersnummer en het aantal boetes van de speler die het grootste aantal boetes heeft. Het SQL-commando: Net als in bovenstaande ' analyse van de informatievraag' splitsen we het tot stand komen van het gezochte SQL-commando op in twee stappen: Stap 1: we bepalen allereerst per beboete speler het aantal boetes via de volgende SQL-query: SELECT FROM GROUP BY

spelersnr, COUNT(*) boetes spelersnr

die de volgende tussentabel oplevert: spelersnr

COUNT(*)

6

1

12

5

25

2

28

2

34

2

42

1

figuur 4.12: tussentabel: de samenvoegingstabel na groepering en telling.


SQL. 37

KUNijmegen


Stap 2: uit de resultaten van deze tussentabel moeten we nu slechts die regel halen, waarvan de (groeps)waarde van COUNT(*) groter of gelijk is aan alle in deze tussentabel voorkomende waarden van COUNT(*). Omdat de selectie op een groepswaarde wordt uitgevoerd, moet het HAVINGcommando worden gebruikt om het gewenste eindresultaat te bereiken. De totale SQL-gegevensvraag wordt daarom: SELECT spelersnr, COUNT(*) FROM boetes GROUP BY spelersnr HAVING COUNT(*) >= ALL ( SELECT FROM GROUP BY met de volgende eindresultaattabel: spelersnr

COUNT(*) boetes spelersnr )

COUNT(*) 5

12

figuur 4.13: de eindresultaattabel: grootste aantal boetes en bijbehorend spelernummer

Voorbeeld 4.7: (Sportvereniging) De informatievraag: Geef de plaatsnaam en het aantal daar wonende spelers van de plaats waar het kleinste aantal spelers woont. Het SQL-commando: (zie ook hoofdstuk 3, paragraaf 3.5.1 en daarbij speciaal figuur 3.32) SELECT plaatsnaam, COUNT(*) FROM spelers GROUP BY plaatsnaam HAVING COUNT(*) <= ALL ( SELECT FROM GROUP BY

COUNT(*) spelers plaatsnaam )

met als eindresultaattabel: Plaatsnaam Arnhem Grave Groesbeek Lent Tiel

COUNT(*) 1 1 1 1 1

figuur 4.14: plaatsen met kleinste aantal spelers.

Relativerende slotopmerking In deze cursus hebben we niet alle mogelijkheden behandeld, die de SQL-taal ons biedt. Níet besproken zijn bijvoorbeeld: correlated subqueries, het gelijktijdig gebruik van meerdere kopieën van een gegevenstabel en: het gebruik van SQL-mogelijkheden om data-integriteit binnen een database af te dwingen. Voor deze aspecten verwijzen we graag naar verdergaande boeken en/of cursussen over SQL.


SQL. 38

KUNijmegen

89 36

Recommend Documents