Spoedcursus SQL met MS-Access

C.T. de Groot J.G. Klein

Spoedcursus SQL met MS-Access

Wolters-Noordhoff Groningen/Houten

Ontwerp binnenwerk: Studio Wolters-Noordhoff, Groningen Wolters-Noordhoff bv voert voor het hoger onderwijs de imprints Wolters-Noordhoff, Stenfert Kroese, Martinus Nijhoff en Vespucci. Eventuele open aanmerkingen over deze of andere uitgaven kunt u richten aan: WoltersNoordhoff bv, Afdeling Hoger Onderwijs, Antwoordnummer 13, 9700 VB Groningen, e-mail: [email protected]

0 1 2 3 4 5 / 10 09 08 07 06 Copyright © 2006 Wolters-Noordhoff bv Groningen/Houten, The Netherlands. Behoudens de in of krachtens de Auteurswet van 1912 gestelde uitzonderingen mag niets uit deze uitgave worden verveelvoudigd, opgeslagen in een geautomatiseerd gegevensbestand of openbaar gemaakt, in enige vorm of op enige wijze, hetzij elektronisch, mechanisch, door fotokopieën, opnamen of enig andere manier, zonder voorafgaande schriftelijke toestemming van de uitgever. Voor zover het maken van reprografische verveelvoudigingen uit deze uitgave is toegestaan op grond van artikel 16h Auteurswet 1912 dient men de daarvoor verschuldigde vergoedingen te voldoen aan Stichting Reprorecht (postbus 3060, 2130 KB Hoofddorp, www.reprorecht.nl). Voor het overnemen van korte gedeelte(n) uit deze uitgave in bloemlezingen, readers en andere compilatiewerken (artikel 16 Auteurswet 1912) kan men zich wenden tot Stichting PRO (Stichting Publicatie- en Reproductierechten Organisatie, postbus 3060, 2130 KB Hoofddorp, www.cedar.nl/pro). Voor het overnemen van niet-korte gedeelte(n) dient men zich rechtstreeks te wenden tot de uitgever. All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form or by any means, electronic, mechanical, photocopying, recording, or otherwise, without the prior written permission of the publisher.

Woord vooraf

Deze syllabus wordt gebruikt om te leren werken met SQL, een vraagtaal die gegevens uit een gegevensverzameling (de database) haalt. Met SQL kunnen de gegevens uit de database op complexe wijze worden gecombineerd om tot de gewenste informatie te komen. Dit zogeheten aggregeren van gegevens tot de gewenste informatie kan binnen het softwarepakket MS-Access van Microsoft op twee verschillende manieren gerealiseerd worden. 1 De eerste mogelijkheid is gebruik te maken van de commando’s van SQL. 2 De tweede mogelijkheid is de gebruiksvriendelijk Query By Example, een sterk gevisualiseerde omgeving waarbij in de executiefase een commando in SQL gegenereerd en uitgevoerd wordt. In deze syllabus worden beide mogelijkheden behandeld. De nadruk ligt op de SQL-commando’s die ook in andere omgevingen dan die van Microsofts MS-Access gebruikt worden. De Query By Example (= QBE) hoort typisch bij MS-Access, in andere softwarepakketten zal de daar gehanteerde QBE er anders uitzien. Voor een goed gebruik van MS-Access is het nodig een aantal termen te kennen. Het overgrote deel wordt in de eerste hoofdstukken van deze syllabus behandeld. Voor de eenvoud van de bestudering zijn deze termen cursief afgedrukt. J.G. Klein, IJsselstein C.T. de Groot, Wijk bij Duurstede

Inhoud

4

1

Access starten, database openen en sluiten 5

2 2.1 2.2 2.3 2.4 2.5 2.6 2.7 2.8 2.9 2.10

Raadplegen van een bestand 8 Een query maken 8 SQL-commando 10 Het sorteren van de uitvoer 13 Het verwijderen van dubbele resultaten 15 Raadplegen met een enkelvoudige conditie 15 De samengestelde conditie 17 De samengestelde conditie met meer dan twee condities 19 Het gebruik van IN, BETWEEN, LIKE 20 Functies in SQL 22 Rekenen in SQL 24

3 3.1 3.2 3.3

Raadplegen van verschillende bestanden 26 Het koppelen van tabellen met een join 26 De sub-query 28 De sub-query, unieke mogelijkheden 30

4 4.1 4.2 4.3 4.4

Groeperen 32 Het definiëren van groepen: GROUP BY 32 Gebruik queryvenster 34 Selecties op groepen: HAVING 36 Gebruik queryvenster by HAVING 36

Bijlage

De database van BitByBit 38


1

Access starten, database openen en sluiten

Start Access via de toolbar. Kies vervolgens voor ‘Een bestaande database openen’ en selecteer de database BitByBit. Nadat de database is geopend wordt het Microsoft Access-venster zichtbaar. Dit venster laat zich opsplitsen in een menubalk (Bestand, Bewerken, Beeld, enzovoort), een databasewerkbalk (met een groot aantal knoppen voor standaardbewerkingen) en een databasevenster dat bij de geopende database hoort. Dit databasevenster bevat de objectknoppen Tabellen, Queries, Formulieren, Rapporten, Macro’s en Modulen. Zie voor de gebruikte termen figuur 1.1. Figuur 1.1

Een venster van Access

Inhoud bestand in gegevensbladweergave Om gegevens van de database te kunnen bekijken, moet je op de knop ‘Tabellen’ klikken. De tabellen die bij de database horen, worden in alfabetische volgorde getoond. Je kunt de gewenste tabel selecteren door er op te klikken. Daarna kun je de inhoud van de tabel bekijken in het venster Gegevensbladweergave. Hiervoor moet je op de knop ‘Openen’ klikken, die je kunt vinden aan de rechterzijde van het databasevenster. Zie figuur 1.2.


5

Figuur 1.2

Het venster ‘Gegevensbladweergave’ in Access

Het venster ‘Gegevensbladweergave’ kan op de gebruikelijke wijze vergroot worden. De aanwijzer kan op verschillende manieren gebruikt worden om de gewenste records en velden zichtbaar te maken.

1.1

Opdracht Open het bestand ‘Klant’ en ga na in welke stad ‘Cohen Gebak’ is gevestigd. Keer vervolgens terug naar het databasevenster. Inhoud bestand met formulierweergave Met Microsoft Access kunnen gegevens op verschillende manieren worden weergegeven. In de zojuist behandelde werkwijze werden de gegevens via de knop ‘Tabellen’ gepresenteerd in de ‘Gegevensbladweergave’. Een andere manier is de zogeheten Formulierweergave. Een voorbeeld is het formulier ‘Klantenformulier’. Probeer dit formulier zelf op te roepen. In figuur 1.3 zie je wat je op je scherm moet krijgen.

6


Figuur 1.3

Klantenformulier

Onder in het venster zitten de navigatieknoppen die op het formulier gebruikt worden om naar een ander record te gaan. In de formulierweergave is duidelijk zichtbaar dat de gegevens van slechts één record worden getoond, terwijl in de gegevensbladweergave verschillende records van een bestand worden gepresenteerd. Het is zeer eenvoudig om te wisselen van de ene naar de andere wijze van presenteren. Daarvoor moet je in de menubalk op de knop ‘Beeld’ klikken en kun je naar believen van de formulierpresentatie naar de gegevenspresentatie gaan en weer terug.


7

2

Een bestand raadplegen

Een bestand moet gebruikt worden. Een manier om het te gebruiken is het raadplegen van een bestand, dat wil zeggen het zoeken van gegevens in het bestand. Het raadplegen vindt plaats op basis van een vraag of query die aan de database wordt gesteld. Dit hoofdstuk en de volgende hoofdstukken handelen over de wijze waarop een query binnen Microsoft Access gemaakt kan worden.

2.1

Een query maken In het databasevenster bevindt zich de knop ‘Query’. Nadat je deze knop hebt geactiveerd, ben je in de gelegenheid een query aan te maken onder de knop ‘Nieuw’, die aan de rechterzijde te vinden is. Je kunt ook met de andere knoppen een eerder gemaakte query activeren of wijzigen. Als ‘Nieuw’ is geactiveerd verschijnt het beeldscherm van figuur 2.1. Figuur 2.1

Een bestand raadplegen

Nadat met de knop ‘OK’ voor ontwerpweergave is gekozen, zoals ook in figuur 2.2 is gedaan, verschijnt het venster ‘Tabel weergeven’.

8


Figuur 2.2

Het scherm Tabel weergeven

Je kunt nu een tabel selecteren die via de knop ‘Toevoegen’ zichtbaar wordt in het bovenste gedeelte van het venster. Je kunt het selecteren en toevoegen voor elke gewenste tabel herhalen. Wordt vervolgens op de knop ‘Sluiten’ geklikt dan ontstaat het zogeheten queryvenster zoals te zien is in figuur 2.3. Figuur 2.3

Queryvenster

Het queryvenster bestaat uit twee delen. In het bovenste deel worden de tabellen getoond waarop de query betrekking heeft. Het onderste deel wordt queryraster genoemd, en daarmee kan de query tot in detail worden opgebouwd.

2.1

Opdracht Voer voorgaande bewerkingen uit en plaats de tabel ‘Producten’ in het queryvenster. Dubbelklik vervolgens op de veldnamen ‘omschrijving’, ‘voorraad’ en ‘prijs’, waardoor deze drie velden opgenomen worden in het queryraster. Met deze opdracht is een zeer eenvoudige query gemaakt waarbij de gegevens over omschrijving, voorraad en prijs geselecteerd zullen worden. Het resultaat van de selectie wordt zichtbaar gemaakt door in de werkbalk de meest linkse knop in te drukken. De


9

tabel die nu verschijnt, wordt in Access dynaset genoemd, wat het antwoord op de query betekent. Je kunt weer terugkeren naar het queryvenster door weer op de meest linkse knop van de werkbalk te klikken. Zie figuur 2.4. Figuur 2.4

Knop Queryweergave

Aan een query kan een naam gegeven worden, waardoor het mogelijk is een volgende keer opnieuw van de query gebruik te maken. De naam wordt pas bij het sluiten van het queryvenster gegeven. Bij de volgende opdrachten word je gevraagd met behulp van het queryvenster een eenvoudige query samen te stellen. Alle hiervoor genoemde stappen moeten bij elke vraag opnieuw worden uitgevoerd.

2.2

2.3 2.4 2.5 2.6

2.2

Opdrachten De secretaresse van BitByBit wil een adressenlijst hebben van alle medewerkers. Maak een adressenlijst waarin achtereenvolgens naam, adres en woonplaats zijn vermeld. Geef de query de naam query1. Maak een lijst van de producten met de omschrijving en de bijbehorende prijs. Geef een overzicht van de namen van de medewerkers en hun basissalaris. Maak een telefoonlijst van alle klanten. Hoeveel klanten zitten er in het klantenbestand? Welke medewerkers met welke functie en op welke afdeling krijgen geen verkoopprovisie, dat wil zeggen hebben een provisiepercentage gelijk aan 0?

SQL-commando Een query die op de hiervoor genoemde wijze gemaakt is, wordt eerst vertaald in een zogeheten SQL-commando. Het SQL-commando wordt vervolgens uitgevoerd om het resultaat van de query zichtbaar te maken, de dynaset. Je kunt het SQL-commando ook goed rechtstreeks intypen, zonder dat je uitgebreid gebruik maakt van het queryvenster. Het rechtstreeks intypen van SQL-commando’s is minder gebruiksvriendelijk, maar daar staat tegenover dat er meer mogelijkheden zijn in SQL om het gewenste resultaat, de dynaset, te krijgen. Anderzijds is het ook zo dat het gebruik van het queryvenster sterk afhankelijk is van de software; het bestaat in deze vorm alleen binnen Microsoft Access. In andere pakketten heeft het queryvenster weer een andere vorm. De achterliggende

10


SQL-commando’s zijn echter hetzelfde. SQL geldt voor alle systemen. Start het queryvenster zoals in de vorige paragraaf is beschreven. Neem een of ander bestand, in het voorbeeld is dit PRODUCTEN maar het kan willekeurig zijn. Druk vervolgens op de rechterknop van de queryweergave in de werkbalk, zoals in figuur 2.5 is weergegeven. Door vervolgens SQL te selecteren kom je in het SQL-weergavescherm. Figuur 2.5

SQL-weergavescherm

In de SQL-weergave kunnen commando’s worden ingetypt. Er staat meestal al een commando, namelijk het commando om de opdracht van het queryvenster uit te voeren. In het voorbeeld verschijnt het, overigens niet correcte, SQL-commando: → SELECT FROM producten; Dit commando kan aan elke wens worden aangepast, bijvoorbeeld om de selectie van de kolommen aan te geven, maar ook om een totaal nieuw SQL-commando in te typen. Je kunt bijvoorbeeld in de eerste regel een sterretje toevoegen, waardoor het wel een correct SQL-commando wordt: → SELECT * FROM producten; Binnen de gegevensbladweergave kun je het resultaat van het commando bekijken. Dit is te vinden door weer op de meest linkse knop van de werkbalk te klikken, net zoals in figuur 2.4 is afgebeeld. De gegevensbladweergave van bovenstaand commando zie je in figuur 2.6.


11

Figuur 2.6

Gegevensbladweergave

Van SQL zijn vele dialecten met elk hun eigen afspraken en notatiewijzen in omloop. Access vertaalt het ontwerp van een query naar een SQL-commando volgens de afspraken van Access-SQL. Het Access-SQL wijkt op enkele plaatsen af van het standaard ANSISQL. Het standaard-SQL wordt echter volledig door Access begrepen. De SQL-commando’s die hier gegeven worden, zijn van het kortere standaardtype. Werkwijze bij de beantwoorden opdrachten De volgende vragen moeten gemaakt worden met behulp van het SQL-weergavescherm. Probeer allereerst het commando uit door het gegevensblad te openen. Noteer het SQLcommando in de ruimte onder de vragen in de korte (ANSI-SQL) notatiewijze. Het resultaat hoeft uiteraard niet overgenomen te worden. In plaats van het SQL-commando op te schrijven kan dit ook gekopieerd en in een Word-document geplakt worden.

2.7 2.8

Opdrachten Maak een lijst waarop alle gegevens van de medewerkers staan. Bekijk het resultaat van de query in de Gegevensbladweergave. Noteer het SQL-commando. Maak een lijst met daarop alle gegevens van de klanten. Noteer het SQL-commando. Gebruik hoofdletters of kleine letters Het maakt niet uit of SQL-queries met hoofdletters dan wel met kleine letters worden ingetoetst. Neem een commando als: SELECT naam, plaats FROM medewerkers ORDER BY naam; In deze syllabus worden SQL-begrippen (SELECT, FROM, ORDER BY enzovoort, dus de woorden van de taal SQL) met hoofdletters geschreven, terwijl de overige tekst met kleine letters wordt geschreven. Hieruit blijkt dat het ook goed is om met alleen maar hoofdletters of alleen maar met kleine letters de commando’s te geven. Samenstellen SQL-commando In het vervolg van het boek zullen we voor het maken van SQL-commando’s gebruikmaken van een aanpak die bestaat uit een viertal stappen: 1 Maak vanuit het databasevenster een nieuwe query en kies in de ontwerpweergave de tabel. 2 Kies vervolgens de velden die getoond moeten worden. 3 Schakel vervolgens naar de SQL-weergave om het SQL-commado aan te passen en/of uit te breiden. 4 Het resultaat van de query wordt zichtbaar gemaakt in de gegevensweergave.

12


2.9 2.10

2.3

Opdrachten Toon van de geregistreerde producten de omschrijving en de bijbehorende voorraad. Noteer het SQL-commando. Geef in een overzicht de namen van de personeelsleden, met daarbij hun datum van indiensttreding.

Het sorteren van de uitvoer Het resultaat van een SQL-commando is in het algemeen ongesorteerd. Indien het resultaat van een SQL-commando wel gesorteerd moet worden, dan kun je gebruikmaken van de toevoeging ORDER BY aan het commando. Neem bijvoorbeeld het commando: → SELECT naam, afdeling, functie FROM medewerkers ORDER BY naam; Het resultaat van dit commando toont een lijst van alle records van de tabel Medewerkers, maar nu gesorteerd.

Naam Brendel Cohen Dirksen Doorman Hendriks Hoeben Jansen Tuinier Winkelman Wofsjong Ytzerda Zachariassen Zweers

Afdeling Verkoop Verkoop Assemblage Financiën Verkoop Verkoop Verkoop Verkoop Magazijn Inkoop Verkoop Verkoop Verkoop

Functie Vertegenwoordiger Vertegenwoordiger Inpakker Administrateur Vertegenwoordiger Vertegenwoordiger Vertegenwoordiger Verkoper Chef Telefonist Vertegenwoordiger Verkoper Vertegenwoordiger

Een veldnaam achter de ORDER BY-component moet ook voorkomen in de SELECT-regel. In voorgaand voorbeeld komt het veld ‘naam’ zowel achter de SELECT voor als achter de ORDER BY. In plaats van een veldnaam achter het onderdeel ORDER BY te zetten kan ook een getal worden gebruikt. Het getal verwijst naar de positie die de veldnaam inneemt achter het SELECT. Neem bijvoorbeeld het commando: → SELECT naam, afdeling, functie FROM medewerkers ORDER BY 2; Hierin verwijst de 2 naar het veld ‘afdeling’, zodat op afdeling gesorteerd zal gaan worden. Een nummer in plaats van de veldnaam zelf is vooral handig als gerefereerd wordt naar een uitgebreide berekening achter de SELECT. Achter de toevoeging ORDER BY mogen ook verschillende veldnamen worden opgenomen, zoals in: → SELECT productsoort, omschrijving, prijs FROM producten ORDER BY productsoort, omschrijving; Het met dit commando verkregen overzicht is tweemaal gesorteerd: eerst op productsoort en daarbinnen op de omschrijving, zie figuur 2.7.


13

Figuur 2.7

Overzicht

In plaats van de veldnamen achter het onderdeel ORDER BY te zetten, kan door middel van getallen worden gerefereerd naar de posities die de veldnamen innemen achter het SELECT-onderdeel: → SELECT functie, naam, plaats FROM medewerkers ORDER BY 1,2;

2.11 2.12 2.13

2.14

Opdrachten Voer het voorgaande commando uit. Let op de wijze waarop bij meervoudig sorteren het resultaat op het scherm wordt afgebeeld. Geef in een overzicht de namen van de personeelsleden, met daarbij hun basissalaris. Sorteer de lijst op achternaam. Noteer het bijbehorende SQL-commando. Er is een verschil tussen de volgende twee commando’s. Noteer het verschil tussen beide commando’s. SELECT plaats, naam FROM klanten ORDER BY 2,1; SELECT plaats, naam FROM klanten ORDER BY 1,2; Noteer het verschil (als er een verschil is) tussen de volgende vier SQL-commando’s: SELECT naam, basissalaris FROM medewerkers ORDER BY basissalaris; SELECT naam, basissalaris FROM medewerkers ORDER BY basissalaris DESC; SELECT naam, basissalaris FROM medewerkers ORDER BY basissalaris ASC; SELECT naam, basissalaris FROM medewerkers ORDER BY 2 ASC, 1 ASC;

14


2.4

Het verwijderen van dubbele resultaten Als resultaat van een SELECT-commando kunnen dubbele rijen worden getoond. Iemand wil een overzicht van welke functies er binnen BitByBit bestaan. Dit overzicht verschijnt op het scherm na een commando als: → SELECT functie FROM medewerkers; Bij een dergelijk commando kunnen dezelfde functies verschillende keren voorkomen. Soms kan het fraaier zijn dit meervoudig voorkomen in de uitvoer te vermijden. Men kan dit elimineren met de DISTINCT-operator: → SELECT DISTINCT functie FROM medewerkers;

2.17

Opdrachten Noteer het commando dat een lijst geeft van verschillende plaatsen waarin klanten van het bedrijf gevestigd zijn. Een plaats mag maar één keer in de lijst voorkomen. Is het overzicht gesorteerd? De vraag is welke klanten orders hebben geplaatst. Noteer het commando dat zo’n lijst maakt, waarbij alleen het klantnummer getoond wordt. Gebruik het bestand ‘Verkooporders’. Welke zijn de verschillende basissalarissen? Sorteer het overzicht!

2.5

Raadplegen met een enkelvoudige conditie

2.15

2.16

In de voorgaande oefeningen werden alle records uit het bestand op het beeldscherm geplaatst. De meeste bestanden bevatten vele records, veel meer in elk geval dan er ruimte op één beeldscherm is. SQL is met name bruikbaar als je een overzicht wilt van een beperkt aantal records. Denk bijvoorbeeld aan de situatie dat iemand alleen maar geïnteresseerd is in de gegevens van de klanten die in Utrecht zijn gevestigd. Wanneer je dit soort selecties gaat maken, kan dat binnen SQL door het onderdeel WHERE toe te voegen aan het SELECT-commando. Met de WHERE-component wordt een voorwaarde of conditie gedefinieerd waarmee men aangeeft welke gegevens moeten worden geraadpleegd. We beginnen met de eenvoudigste vorm: de enkelvoudige conditie. Wanneer een conditie wordt samengesteld, kan gebruik worden gemaakt van zes operatoren: 1 < is kleiner dan; 2 > is groter dan; 3 = is gelijk aan; 4 <= is kleiner dan of gelijk aan; 5 >= is groter dan of gelijk aan; 6 <> is ongelijk aan. In het volgende voorbeeld wordt een commando gegeven dat een lijst produceert van alle artikelen waarvan de huidige voorraad vrij klein is, in dit geval minder dan zes stuks: → SELECT omschrijving, prijs, voorraad FROM producten WHERE voorraad < 6;


15

Omschrijving Geluidskaart Pentium 90 mh Pentium 120 mh Pentium 300 mh Pc 486-dx 150 mh PowerSystem P100 PowerSystem P120

Prijs € 230,00 € 2.010,00 € 2.450,00 € 2.900,00 € 1.200,00 € 690,00 € 780,00

Voorraad 5 5 3 5 2 4 3

Het veld ‘voorraad’ is een numeriek datatype. Met een numeriek datatype kan worden gerekend. In de voorraadkolom is te zien dat aan het criterium: voorraad <6 wordt voldaan. Het SQL-commando kan ook met de ontwerpweergave worden gemaakt, zoals te zien is in figuur 2.8. Figuur 2.8

Commando in Ontwerpweergave

Het volgende voorbeeld is een commando dat een gesorteerde lijst produceert van klanten die in Landsmeer zijn gevestigd: → SELECT naam, adres, plaats FROM klanten WHERE plaats = “Landsmeer” ORDER BY naam; Probeer dit commando op te bouwen via het queryvenster: Het veld ‘plaats’ is een alfanumeriek datatype. De waarde die een alfanumeriek veld kan aannemen wordt omsloten door aanhalingstekens. Het resultaat van de query kan weer zichtbaar worden gemaakt in de gegevensbladweergave.

16


Naam Accent Uitgevers Basis Centrum Lasschuyt Q.A.

2.18 2.19 2.20

Adres Hoogheuvelweg 20 Kraanenstraat 76 Salieweg 83

Plaats Landsmeer Landsmeer Landsmeer

Opdrachten Maak een lijst van de klanten die gevestigd zijn in Sappemeer. Zet op de uitvoer de klantnaam, het adres en de vestigingsplaats. Noteer het SQL-commando. Van welke artikelen zijn er meer dan 30 stuks in voorraad? Noteer het SQL-commando. Welke verkooporders hebben een orderbedrag van meer dan 20.000 euro? Noteer het SQL-commando. Datumveld Het volgende voorbeeld is bedoeld om aan te geven hoe je gebruik kunt maken van een datumveld. De vraag is: welke medewerkers zijn al vóór juli 1988 in dienst getreden? De datum wordt ingevoerd als: 01/07/1988. Het SQL-commando is: → SELECT naam, afdeling, functie, datumin FROM medewerkers WHERE datumin<#1/7/88#;

Naam Winkelman Ytzerda Doorman

Adeling Magazijn Verkoop Financiën

Functie Chef Vertegenwoordiger Administrateur

Datumin 1/24/85 5/17/85 12/2/86

Het veld ‘datumin’ is van het datatype datum. De waarde die de datum kan aannemen, moet dan worden omsloten door hekjes, zoals #1/7/88#. Let op: een datumveld moet altijd een echt bestaande datum bevatten. De datum #0/0/88# bijvoorbeeld bestaat niet en geeft een foutmelding. Ook de datum #30/2/88# behoort tot de foutieve data.

2.21 2.22

Opdrachten Welke personeelsleden zijn na 1991 in dienst getreden? Noteer het SQL-commando. Maak een lijst met verkooporders die na 2002 geplaatst zijn.

2.6

De samengestelde conditie Uiteraard is het nodig dat je commando’s kunt geven met een samengestelde conditie. Een samengestelde conditie is een conditie die aan meer dan één voorwaarde moet voldoen. Denk bijvoorbeeld aan de medewerker van de afdeling Verkoop die een overzicht wil maken van al zijn verkooporders in een bepaalde week met een orderbedrag boven de 10.000 euro. De twee condities zijn als eerste de afdeling Verkoop en als tweede het orderbedrag groter dan 10.000. Wanneer je in SQL een samengestelde conditie wilt definiëren, moet je gebruikmaken van de operatoren AND of OR of van een combinatie ervan. Allereerst worden in deze paragraaf SQL-opdrachten behandeld die uit twee condities bestaan. AND-operator De heer Winkelman heeft het idee dat de magazijnvoorraad van een aantal goederen een hoog bedrag vertegenwoordigt. Hij wil daarom een lijst maken van artikelen waarvan de verkoopprijs 500 euro of meer bedraagt en waarvan er meer dan 10 stuks in voor-


17

raad zijn. Voor de beantwoording van deze vraag wordt van de AND-operator gebruikgemaakt: → SELECT omschrijving, prijs, voorraad FROM producten WHERE prijs >= 500 AND voorraad > 10;

Omschrijving Beeldscherm VGA HardDisk 5,2Gb PowerSystem P170

Prijs € 745,00 € 4.603,00 € 1.350,00

Voorraad 40 12 14

OR-operator Een volgend voorbeeld toont het gebruik van de OR-operator. Stel, het is nu 12 februari 1997. De vraag is welke klanten gisteren of eergisteren orders hebben geplaatst. Het commando dat het gewenste antwoord geeft, is: → SELECT klantnr, orderdat FROM verkooporders WHERE orderdat = #11/02/2003# OR orderdat = #10/02/2003#;

2.23 2.24

2.25

2.26

18

Opdrachten Geef het commando waarmee een overzicht wordt gegeven van de verkooporders in week 14 waarbij het orderbedrag boven de 10.000 euro uitsteeg. Gevraagd wordt: een overzicht van alle klanten in Loosdrecht en Weesp. Welke van de twee commando’s is het juiste? SELECT naam, plaats FROM klanten WHERE plaats = ‘Loosdrecht’ AND plaats = ‘Weesp’; SELECT naam, plaats FROM klanten WHERE plaats = ‘Loosdrecht’ OR plaats = ‘Weesp’; Wat is het verschil tussen de volgende twee commando’s? SELECT omschrijving, prijs, voorraad FROM producten WHERE prijs >= 100 AND voorraad > 10; SELECT omschrijving, prijs, voorraad FROM producten WHERE prijs >= 100 OR voorraad > 10; Welke verkooporders zijn er geplaatst in de periode 26 augustus 2002 tot en met 5 september 2002? Noteer het SQL-commando.


2.27

Maak een overzicht waarin de namen, de functie en de afdeling van de vertegenwoordigers en de verkopers staan vermeld. Sorteer het overzicht op naam van de medewerker.

2.7

De samengestelde conditie met meer dan twee condities Het is natuurlijk ook mogelijk om meer dan twee voorwaarden te koppelen in één samengestelde conditie. Daarbij kunnen de logische operatoren AND en OR één of verschillende malen, al of niet gecombineerd, voorkomen. Neem bijvoorbeeld de volgende situatie waarin drie condities verscholen zitten: Een functionaris van personeelszaken wenst een lijst van alle vertegenwoordigers met een basissalaris tussen de 40.000 en 50.000 euro. Het SQL-commando dat antwoord op de vraag geeft is: → SELECT naam, functie, basissalaris, datumin FROM medewerkers WHERE functie = ‘Vertegenwoordiger’ AND basissalaris >= 40000 AND basissalaris <= 50000;

Naam Hendriks Brendel Hoeben Zweers Jansen Cohen

Functie Vertegenwoordiger Vertegenwoordiger Vertegenwoordiger Vertegenwoordiger Vertegenwoordiger Vertegenwoordiger

Basissalaris € 45.000,00 € 45.000,00 € 45.000,00 € 45.000,00 € 45.000,00 € 47.000,00

Datumin 15-2-88 7-5-89 5-11-90 18-4-91 2-6-91 12-5-93

In een samengestelde conditie met meer dan twee voorwaarden moet je heel vaak gebruikmaken van haakjes om het juiste commando te kunnen geven. Als we een lijst wensen van alle Vertegenwoordigers uit Gouda of Delft dan levert het volgende SQLcommando wel een lijst (immers, het commando is syntactisch juist gesteld), maar het is helaas de verkeerde: → SELECT naam, plaats, functie FROM medewerkers WHERE plaats = ‘Gouda’ OR plaats = ‘Delft’ AND functie = ‘Vertegenwoordiger’;

2.28

Opdrachten Voer dit commando uit. In het resultaat van het commando op het scherm is te zien dat er medewerkers zijn die niet tot de vertegenwoordigers behoren. Dat is niet gevraagd, het commando is daarom onjuist. Het commando kan wel correct worden gemaakt door op de juiste plek haakjes te zetten. Neem bijvoorbeeld de twee volgende commando’s: → SELECT naam, plaats, functie FROM medewerkers WHERE (plaats = ‘Gouda’ OR plaats = ‘Delft’) AND functie = ‘Vertegenwoordiger’; → SELECT FROM WHERE OR AND

naam, plaats, functie medewerkers plaats = ‘Gouda’ (plaats = ‘Delft’ functie = ‘Vertegenwoordiger’);


19

2.29

Welke van de twee commando’s uit opdracht 2.27 geeft wel de juiste lijst? Een belangrijke afspraak is: Gebruik in een samengestelde conditie waarin een OR-operator zit altijd haakjes om de bedoeling duidelijk te maken. Beter is, indien mogelijk, om de IN-operator te gebruiken. Deze operator wordt in de volgende paragraaf behandeld.

2.30

Welke medewerkers zijn in 1991 of 1992 in dienst gekomen als vertegenwoordiger of als verkoper? Plaats op dit overzicht alleen de naam, de functie en de datum van indiensttreding. Noteer het SQL-commando.

2.8

Het gebruik van IN, BETWEEN, LIKE Soms is het wenselijk bij een samengestelde conditie een aantal mogelijke waarden voor een veld op te geven. Zo zouden we een lijst kunnen maken van klanten in zes gemeenten: → SELECT naam, adres, plaats FROM klanten WHERE plaats = ‘Loosdrecht’ OR plaats = ‘Mijdrecht’ OR plaats = ‘Landsmeer’ OR plaats = ‘Weesp’ OR plaats = ‘Bussum’ OR plaats = ‘Abcoude’;

Naam Algemene Kantoor Interieur Totaal Accent Uitgevers Countinho Continu Lada Rijgenot Basis Centrum Akkerman D. Lasschuyt Q.A.

Adres Grootestraat 56 Kennemerweg 89 Hoogheuvelweg 20 Colderweg 56 Visstraat 60 Kraanenstraat 76 Leijenburglaan 96 Salieweg 83

Plaats Loosdrecht Mijdrecht Landsmeer Mijdrecht Loosdrecht Landsmeer Weesp Landsmeer

Het is mogelijk dergelijke commando’s te vervangen door een ander en zeer handig SQLcommando met een IN-operator: → SELECT naam, adres, plaats FROM klanten WHERE plaats IN (‘Loosdrecht’, ‘Mijdrecht’, ‘Landsmeer’, ‘Weesp’, ‘Bussum’, ‘Abcoude’); Voorgaand voorbeeld laat zien dat het mogelijk is een verzameling in het commando op te nemen, in dit geval de verzameling van gewenste steden. Binnen SQL kan een IN-operator gecombineerd worden met de NOT-operator. Neem bijvoorbeeld het volgende commando om een lijst van adressen te krijgen van klanten die nu juist niet in de verzameling plaatsen wonen: → SELECT naam, adres, plaats FROM klanten WHERE plaats NOT IN (‘Loosdrecht’, ‘Mijdrecht’, ‘Landsmeer’,’Weesp’, ‘Bussum’, ‘Sappemeer’) ORDER BY plaats;

20


Naam Broeckhove T.H. Nieuwveen K.G. Van der Valk Complex Tellegen W.U. Theo Meubelpaleis Leonardo P.P. Bakker Kantoor Touber Vleespaleis Classique Stijlinter Alleman service Commelin Feestart Expert Service Cuypers Y.

Adres Waterstraat 2 Rondweg 125 Ventweg 101 Wijdeblik 60 Piersmaweg 101 Kasteelweg 50 Teylerslaan 67 Benedictijn 45 Eiberlaan 201 Bredesloot 40 Gijp 140 Meierij 406 Pijnackerstraat 64

Plaats Almelo Castricum Elspeet Nieuw-Vennep Nieuw-Vennep Oirschot Petten Rolde St. Pancras St. Pancras Vierhuizen Winschoten Yerseke

Ook de BETWEEN-operator maakt sommige SQL-formuleringen eenvoudiger en daardoor beter leesbaar. De volgende twee SQL-commando’s geven hetzelfde resultaat op het beeldscherm: → SELECT naam, functie, basissalaris, datumin FROM medewerkers WHERE basissalaris >= 40000 AND basissalaris <= 50000; → SELECT FROM WHERE

naam, functie, basissalaris, datumin medewerkers basissalaris BETWEEN 40000 AND 50000;

Naam Wofsjong Hendriks Brendel Dirksen Hoeben Zweers Jansen Tuinier Cohen Zachariasse

Functie Telefonist Vertegenwoordiger Vertegenwoordiger Inpakker Vertegenwoordiger Vertegenwoordiger Vertegenwoordiger Verkoper Vertegenwoordiger Verkoper

Basissalaris € 45.000,00 € 45.000,00 € 45.000,00 € 41.000,00 € 45.000,00 € 45.000,00 € 45.000,00 € 41.000,00 € 47.000,00 € 45.000,00

Datumin 5-1-92 15-2-88 7-5-89 9-10-89 5-11-90 18-4-91 2-6-91 13-5-92 12-5-93 17-7-93

De BETWEEN is duidelijker en handiger in het gebruik. Ook de BETWEEN-operator mag weer worden gecombineerd met de NOT-operator, zoals in het volgend voorbeeld te zien is: → SELECT naam, functie, datumin FROM medewerkers WHERE datumin NOT BETWEEN #01/01/1988# AND 01/01/1991# ORDER BY datumin;


21

Naam Winkelman Ytzerda Doorman Zweers Jansen Wofsjong Tuinier Cohen Zachariassen

Functie Chef Vertegenwoordiger Administratie Vertegenwoordiger Vertegenwoordiger Telefonist Verkoper Vertegenwoordiger Verkoper

Datumin 24-1-85 17-5-85 2-12-86 18-4-91 2-6-91 5-1-92 13-5-92 12-5-93 17-7-93

Het zoeken in de database lukt alleen als men precies weet wat men moet vinden. Als bijvoorbeeld een spelfout in de naam zit zijn de gegevens bijna niet meer te achterhalen. De LIKE-operator vangt dit probleem voor een groot gedeelte op. Zo is het mogelijk dat iemand gegevens moet hebben van een klant van wie de naam niet meer precies bekend is, maar van wie men nog wel weet dat er Valk in voorkwam. Met het gebruik van de LIKE-operator in een voorwaarde is het mogelijk slechts een gedeelte van een veldwaarde op te geven: → SELECT naam, adres, plaats FROM klanten WHERE naam LIKE ‘*Valk*’;

Naam Van der Valk Complex

Adres Ventweg 101

Plaats Elspeet

De *-tekens geven aan dat zowel voor als achter de letters ‘Valk’ willekeurige tekencombinaties kunnen voorkomen. Het *-teken wordt een joker of wildcard genoemd.

2.31

2.32 2.33

2.9

Opdrachten Welke medewerkers hebben een modaal basissalaris van tussen de 45.000 en 50.000 euro? Gebruik BETWEEN. Vraag: worden de grenswaarden wel of niet meegerekend? Noteer het SQL-commando. Welke klanten met ‘Kantoor’ in de naam zijn er in het klantenbestand opgenomen? Gebruik LIKE. Welke medewerkers wonen niet in één van de vier grote steden Dordrecht, Gouda, Rotterdam en Delft? Gebruik NOT IN.

Functies in SQL De tot nu behandelde SQL-commando’s leverden informatie op die gepresenteerd werd in de vorm van rijen uit de verschillende bestanden. De commando’s zijn nog niet voldoende om alle gewenste informatie uit de database te halen. Het simpel tellen van records is bijvoorbeeld alleen mogelijk door gebruik te maken van de zogeheten functies binnen SQL. Er zijn vijf functies gedefinieerd waarmee je acties, zoals berekeningen, kunt uitvoeren op de gegevenswaarden binnen een kolom: AVG SUM MIN MAX COUNT

22

-

gemiddelde som minimum maximum aantal


De standaardfuncties leveren allemaal een waarde op in plaats van een reeks waarden bij de eerdergenoemde commando’s. AVG Neem bijvoorbeeld de vraag wat het gemiddelde salaris is van alle medewerkers, dan is het commando: → SELECT AVG(basissalaris) FROM medewerkers;

Expr1000 € 53.230,77

We zien dat de kolom ‘basissalaris’ waarover het gemiddelde wordt berekend, vermeld moet worden tussen de haakjes. De gegevenswaarden in de kolom waarover het gemiddelde berekend wordt, moeten uiteraard getallen bevatten. Neem het commando: → SELECT AVG(basissalaris) FROM medewerkers WHERE afdeling = ‘Verkoop’; Hierbinnen worden eerst alle rijen geselecteerd waarin de gegevens van de verkopers zijn geregistreerd. Vervolgens wordt uit die verzameling het gemiddelde berekend van de gegevenswaarden van de kolom ‘basissalaris’:

Expr1000 € 48.444,44

SUM De gegevenswaarden waarover gesommeerd wordt, moeten ook numeriek zijn. Neem voor het gebruik van de SUM het volgende voorbeeld: het basissalaris in het bestand MEDEWERKERS is het salaris op jaarbasis, afgezien van de provisie. De volgende query berekent het totaal aan vaste salariskosten dat jaarlijks aan de medewerkers wordt betaald: → SELECT SUM(basissalaris) FROM medewerkers;

Expr1000 € 692.000,00

MAX en MIN De gevenswaarden waarvan de maximale of minimale waarde bepaald wordt, hoeven niet per se numeriek te zijn, ze mogen ook data of namen bevatten. Wil je bijvoorbeeld weten wat het hoogste basissalaris is dan wordt de query: → SELECT MAX(basissalaris) FROM medewerkers;

Expr1000 € 89.000,00


23

De volgende query geeft de prijs van het goedkoopste artikel: → SELECT MIN(prijs) FROM producten;

Expr1000 € 99,75

COUNT De functie telt het aantal records, geen gegevenswaarde. Wil iemand bijvoorbeeld weten hoeveel medewerkers er momenteel in dienst zijn, dan wordt het commando: → SELECT COUNT(*) FROM medewerkers;

Expr1000 13

Achter de functie COUNT staat tussen de haakjes een ‘*’ vermeld. Dit komt omdat men het aantal records wil tellen. Het symbool ‘*’ staat eigenlijk voor het gehele record.

2.35 2.36 2.37 2.38 2.39

Opdrachten Hoeveel verkooporders zijn er in de eerste helft van 2002 geplaatst? Noteer het SQL-commando. Wat is het totaalbedrag van deze verkooporders? Wat is het gemiddelde bedrag aan verkooporder in 2002? In welke verschillende steden zijn klanten van BitByBit gevestigd? Wat zijn de totale loonkosten per jaar, uitgaande van de basissalarissen? Wat is het gemiddelde salaris van de Vertegenwoordigers?

2.10

Rekenen in SQL

2.34

In de SELECT- en in de WHERE-component mogen rekenkundige bewerkingen voorkomen. De rekenkundige operatoren + , - , * en / kunnen voor dit doel worden gebruikt. Deze operatoren staan achtereenvolgens voor: + optellen aftrekken * vermenigvuldigen / delen In het volgende voorbeeld wordt gebruikgemaakt van de rekenkundige operatoren. De heer Winkelman is geïnteresseerd in de artikelen die een hoge magazijnwaarde vertegenwoordigen. De vraag is een lijst van alle artikelen te geven, gesorteerd op de waarde van de voorraad: → SELECT omschrijving, prijs, voorraad, prijs*voorraad FROM producten ORDER BY 4;

24


Omschrijving Geluidskaart PowerSystem P120 Muis Pc 486-dx 150 mh PowerSystem P100 HardDisk 2,2Gb Toetsenbord PowerSystem P150 Pentium 120 mh PowerSystem P160 PowerSystem P200 Pc 486-dx 120 mh Pentium 90 mh Pentium 300 mh Pentium 200 mh PowerSystem P170 Beeldscherm VGA HardDisk 5,2Gb Mini Multi Media Mini Multi Media

2.40

Prijs € 230,00 € 780,00 € 99,75 € 1.200,00 € 690,00 € 330,00 € 124,00 € 960,00 € 2.450,00 € 1.150,00 € 1.550,00 € 1.100,00 € 2.010,00 € 2.900,00 € 2.600,00 € 1.350,00 € 745,00 € 4.603,00 € 9.550,00 € 10.250,00

Voorraad 5 3 24 2 4 14 45 7 3 7 6 9 5 5 6 14 40 12 7 9

Expr1 € 1.150,00 € 2.340,00 € 2.394,00 € 2.400,00 € 2.760,00 € 4.620,00 € 5.580,00 € 6.720,00 € 7.350,00 € 8.050,00 € 9.300,00 € 9.900,00 € 10.050,00 € 14.500,00 € 15.600,00 € 18.900,00 € 29.800,00 € 55.236,00 € 66.850,00 € 92.250,00

Opdrachten Wat is het totaal van de salariskosten dat maandelijks aan de medewerkers wordt uitbetaald? Ga uit van de basissalarissen.


25

3

Raadplegen van verschillende bestanden

Tot nu toe hadden alle SELECT-commando’s betrekking op het raadplegen van gegevens uit slechts één bestand. In dit hoofdstuk wordt besproken hoe je een SQL-commando kunt construeren als de informatie in verschillende bestanden is opgeslagen. Daarvoor biedt SQL twee mogelijkheden: de JOIN en de SUB-QUERY.

3.1

Het koppelen van tabellen met een join Een database bestaat uit verschillende tabellen met een zeer duidelijke structuur: de tabellen zijn met relaties aan elkaar gekoppeld. In hoofdstuk 2 waren alle queries van toepassing op slechts één tabel. Bij een join worden twee of verschillende tabellen samengevoegd tot een tijdelijk bestand, waarop de reeds behandelde SQL-commando’s toegepast kunnen worden. Bestanden kunnen alleen op zinvolle wijze samengevoegd worden als er gegevenswaarden zijn die in zowel het ene als in het andere bestand voorkomen. Binnen Access kan op zeer eenvoudige wijze een JOIN-constructie gemaakt worden. Om de JOIN samen te stellen wordt gebruikgemaakt van de ontwerpweergave (queryvenster). Allereerst worden in het queryvenster de tabellen toegevoegd waarop de vraag betrekking heeft. De relatie tussen de tabellen zal door Access in het queryvenster worden getoond. Het gebruik van de join van twee tabellen wordt met een voorbeeld toegelicht. De manager van de afdeling Verkoop is geïnteresseerd in de namen van de medewerkers die in week 21 een verkooporder noteerden. Voor het antwoord op deze vraag zal in twee tabellen gezocht moeten worden. In de tabel ‘Verkooporders’ zal het weeknummer moeten worden gezocht, terwijl in de tabel ‘Medewerkers’ de namen van de medewerkers moeten worden gevonden. Voor het maken van een commando zijn de tabellen Verkooporders en Medewerkers nodig. Zie figuur 3.1. Figuur 3.1

26

Queryvenster voor JOIN


In het queryvenster is de relatie tussen de twee tabellen zichtbaar geworden. Uiteraard is Access daartoe in staat enkel en alleen omdat de twee tabellen een gemeenschappelijk veld bevatten: Mednr. In het queryraster kunnen de attributen worden opgegeven die in het uiteindelijke resultaat zichtbaar moeten worden (Naam en Weeknr), alsmede het criterium (weeknr=21) dat in de vraagstelling is opgesloten. Deze toevoegingen geven het volgende resultaat (zie figuur 3.2). Figuur 3.2

Een JOIN met een voorwaarde

Het resultaat van de query is:

Naam Hendriks Zachariassen Zweers

Weeknr 21 21 21

De query van het queryvenster wordt in een een SQL-commando vertaald. Voor voorgaande join is het SQL-commando: → SELECT DISTINCTROW medewerkers.naam, verkooporders.weeknr FROM medewerkers INNER JOIN verkooporders ON medewerkers.mednr = verkooporders.mednr WHERE (((verkooporders.weeknr)=21)); Van SQL zijn vele dialecten met elk hun eigen afspraken en notatiewijzen in omloop. MSAccess vertaalt het ontwerp van een query naar een SQL-commando volgens de afspraken van Access-SQL. Daarbij vallen drie zaken op: 1 In de SELECT-regel plaatst Access altijd volledig de tabelnaam + veldnaam. 2 In de FROM-regel worden de tabellen genoemd waarop de vraag betrekking heeft. Bovendien wordt in de regel ook de relatie tussen de bestanden aangegeven (de JOIN gemaakt). 3 In de WHERE-regel worden de criteria opgenomen.


27

Het voorgaande SQL-commando kan worden vervangen door het kortere ANSI-SQL: → SELECT naam, weeknr FROM medewerkers, verkooporders WHERE medewerkers.mednr = verkooporders.mednr AND verkooporders.weeknr=21;

3.1

3.2

3.3 3.4

3.5

3.2

Opdrachten Maak een lijst waarop de namen, adressen, vestigingsplaatsen en orderdatum staan vermeld van de klanten die een verkooporder hebben geplaatst van meer dan 30.000 euro. Sorteer het resultaat op orderdatum. Noteer het SQL-commando. Men wil een lijst verkrijgen van de klanten die in week 21 een verkooporder hebben geplaatst. Op deze lijst moeten zowel de naam als het klantnummer van de desbetreffende klanten worden getoond. Noteer de JOIN-constructie. Maak een lijst waarop de namen en de klantennummers vermeld staan van de klanten die een order hebben geplaatst van meer dan 12.000 euro. Noteer het juiste commando. Maak een lijst met naam, adres, plaats, postcode en klantnummer van ‘goede klanten’. Dat zijn klanten die orders hebben geplaatst met een orderbedrag groter dan 20.000 euro. De heer Hendriks heeft als verkoper goede resultaten geboekt. Geef de namen van de klanten, de orderdata en de orderbedragen van de verkooporders die door zijn toedoen een verkooporder hebben geplaatst. Sorteer het overzicht. Raadpleeg voor deze query drie tabellen. Noteer het SQL-commando.

De sub-query Een sub-query is een vraag binnen een vraag. Dit zal allereerst worden toegelicht met een voorbeeld van twee aparte commando’s om het gewenste resultaat te verkrijgen. Indien men bijvoorbeeld een overzicht wil van de namen van alle medewerkers die in week 13 een verkooporder noteerden, dan kan dat gerealiseerd worden door twee queries te geven: → SELECT mednr FROM verkooporders WHERE weeknr=13; Vervolgens noteert men het resultaat op een kladpapiertje: (214, 718, 380) en wordt de tweede query opgesteld om het uiteindelijk gewenste resultaat te verkrijgen: → SELECT naam FROM medewerkers WHERE mednr IN (214, 718, 380); Deze twee commando’s kunnen worden gecombineerd in één SQL-commando: → SELECT naam FROM medewerkers WHERE mednr IN (SELECT mednr FROM verkooporders WHERE weeknr=13); Merk op dat het oorspronkelijke tweede commando als eerste wordt opgenomen. Het oorspronkelijke eerste commando wordt de sub-query! Het commando levert direct het gewenste resultaat, zonder dat je gebruik hoeft te maken van een kladpapiertje:

28


Naam Hoeben Tuinier Zachariassen

Een ander voorbeeld is het volgende. Men wil een lijst maken met de namen van de personeelsleden die in week 21 verkooporders van meer dan 20.000 euro hebben genoteerd. Het commando is: → SELECT naam FROM medewerkers WHERE mednr IN (SELECT mednr FROM verkooporders WHERE weeknr=21 AND orderbedrag>20000);

Naam Zweers

De sub-query levert een verzameling van slechts 1 personeelslid dat aan de twee criteria (weeknummer = 21 en orderbedrag>20000) voldoet. Vervolgens wordt de naam van deze medewerker getoond. Een belangrijke afspraak bij het gebruik van de sub-query is: Achter de SELECT van de sub-query mag slechts één kolomnaam worden opgenomen. Dit is dezelfde kolomnaam als voor de IN-operator is opgenomen. De voorgaande query voldoet aan deze regel: achter de SELECT van de sub-query staat mednr. Ook voor de IN is deze kolomnaam opgenomen. Via mednr wordt in dit voorbeeld een relatie gelegd tussen de tabellen MEDEWERKERS en VERKOOPORDERS. Opdracht Open het queryvenster, kies voor ontwerpweergave, sluit vervolgens het venster tabel weergeven en kies met de knop query-weergave voor SQL-weergave. Geef het volgende SQL-commando: → SELECT klantnr, naam FROM klanten WHERE klantnr IN (SELECT klantnr FROM verkooporders WHERE weeknr=21);

Klantnr 100014 100024 100038

Naam Leonardo P.P. Broeckhove T.H. Adams M.A.


29

3.3

De sub-query, unieke mogelijkheden De vragen die in de vorige paragraaf met een suq-query werden opgelost hadden ook heel goed met een JOIN kunnen worden gemaakt. Bij veel queries waarbij gezocht wordt in verschillende tabellen kun je kiezen van welke techniek je gebruik wenst te maken: de join of de sub-query. Voor een tweetal typen vraagstukken geldt dat er geen gebruik kan worden gemaakt van een join, maar dat gebruikgemaakt móét worden van een sub-query. Het eerste soort vraagstuk bevat een functie, het tweede soort vraagstuk bevat een ontkenning, een negatie. Neem bijvoorbeeld de vraag: Wie verdient het hoogste salaris? Deze wordt beantwoord met het commando: → SELECT naam, basissalaris FROM medewerkers WHERE basissalaris IN (SELECT MAX(basissalaris) FROM medewerkers);

Naam Winkelman

Basissalaris € 89.000,00

In voorgaande query wordt in een commando tweemaal dezelfde tabel geraadpleegd. Allereerst wordt de laatste query geëvalueerd waaruit blijkt dat het hoogste salaris voorkomt in de tabel Medewerkers: 89000. Dit getal wordt als tussenresultaat opgeslagen. Vervolgens wordt de eerste query door Access afgehandeld: welke medewerker(s) verdienen evenveel als het tussenresultaat (89000)?

3.6 3.7 3.8 3.9

Opdrachten Welk product is het goedkoopst? Welke medewerker is het langst in dienst? Welke factuur is het laatst betaald? Welke klant heeft de order geplaatst met het hoogste orderbedrag? Geef klantnaam en orderbedrag. De tweede soort vraagstukken waarbij een sub-query gebruikt moet worden is te herkennen aan de ontkenning in de vraagstelling (ontkenning wordt ook wel negatie genoemd). Een voorbeeld van een dergelijke vraag is: Welke klanten hebben in 2005 geen orders geplaatst? Het commando dat antwoord op de vraag geeft ziet er als volgt uit: → SELECT naam, adres, plaats FROM klanten WHERE klantnr NOT IN (SELECT klantnr FROM verkooporders WHERE orderdat BETWEEN #01/01/2004# AND #31/12/2004#); Allereerst onderzoekt Access het tweede gedeelte van de query en maakt een tussenresultaat in de vorm van een verzameling van alle klantnummers die voorkomen in de verkooporders van 2004. Vervolgens zoekt Access naar de adresgegevens van de klanten van wie het klantnummer NIET voorkomt in het tussenresultaat.

30


3.10 3.11

Opdrachten Welke medewerkers van de Verkoopafdeling hebben in januari 2006 geen orders geboekt? Van welke producten is afgelopen jaren niets verkocht?


31

4

Groeperen

Een belangrijke mogelijkheid binnen SQL is om gegevens die op een of andere wijze bij elkaar horen te clusteren. De clusters kunnen dan gebruikt worden om er operaties op los te laten, zoals sommeren of het gemiddelde nemen. De uitkomst zal alleen voor het cluster gelden. Een andere cluster zal een andere uitkomst van de functie opleveren. Eerst zal besproken worden wat een cluster of groep is.

4.1

Het definiëren van groepen: GROUP BY Stel dat je een overzicht wil maken van het aantal klanten in elke plaats. We kunnen deze informatie krijgen door achtereenvolgens queries te maken als: → SELECT COUNT(*) FROM klanten WHERE plaats = ‘Loosdrecht’; → SELECT FROM WHERE

COUNT(*) klanten plaats = ‘Weesp’;

→ SELECT FROM WHERE

COUNT(*) klanten plaats = ‘Yerseke’;

Het is duidelijk dat het intypen van een commando voor elke plaats een zeer omslachtige methode is. Met de GROUP BY toevoeging is het binnen SQL mogelijk dit soort overzichten met een commando te genereren. De toevoeging GROUP BY groepeert individuele rijen tot verzamelingen van rijen: de groepen. Om de hiervoor genoemde vraag te beantwoorden is een GROUP BY-toevoeging nodig met daarnaast een COUNT-functie in de eerste regel van het commando. Het SQL-commando wordt: → SELECT plaats, COUNT(*) FROM klanten GROUP BY plaats;

32


Plaats Almelo Castricum Elspeet Landsmeer Loosdrecht Mijdrecht Nieuw-Vennep Oirschot Petten Rolde Sappemeer St. Pancras Vierhuizen Weesp Winschoten Yerseke

Expr1001 1 1 1 3 2 2 2 1 1 1 17 2 1 1 1 1

Door de GROUP BY wordt gegroepeerd naar plaats. Per plaats wordt het aantal klanten geteld en met de plaats weergegeven in de lijst. In het volgende voorbeeld wordt het aantal medewerkers per afdeling geteld: → SELECT afdeling, COUNT(*) FROM medewerkers GROUP BY afdeling;

Afdeling Assemblage Financiën Inkoop Magazijn Verkoop

Expr1001 1 1 1 1 9

Indien met de GROUP BY groepen worden gedefinieerd moet altijd een van de volgende functies worden gebruikt: COUNT(*) tellen SUM optellen AVG gemiddelde bepalen MAX maximum bepalen MIN minimum bepalen In het volgende voorbeeld wordt per afdeling het totaal van de loonkosten berekend. De SUM-expressie is nodig omdat de salarissen van de medewerkers gegroepeerd per afdeling bij elkaar worden opgeteld: → SELECT afdeling, SUM(basissalaris) FROM medewerkers GROUP BY afdeling;


33


Expr1001 € 41.000,00 € 81.000,00 € 45.000,00 € 89.000,00 € 436.000,00

Het verschil tussen COUNT(*) en SUM blijkt goed uit het volgende voorbeeld: → SELECT functie, COUNT(*), SUM(basissalaris) FROM medewerkers GROUP BY functie ORDER BY 3;

Functie Inpakker Telefonist Administratie Verkoper Chef Vertegenwoordiger

Expr1001 1 1 1 2 1 7

Expr1002 € 41.000,00 € 45.000,00 € 81.000,00 € 86.000,00 € 89.000,00 € 350.000,00

Uit voorgaand overzicht blijkt bijvoorbeeld dat er twee medewerkers werken in de Binnendienst die samen 86.000 euro verdienen. Het overzicht is gesorteerd op de derde kolom. Een belangrijke regel bij het gebruik van GROUP BY is dat de veldnamen achter de GROUP BY ook moeten voorkomen achter het SELECT-onderdeel. In voorgaand voorbeeld komt de veldnaam ‘functie’ zowel achter de SELECT als achter de GROUP BY voor.

4.2

Gebruik queryvenster Bij het samenstellen van een GROUP BY kan binnen Access handig gebruikgemaakt worden van het queryvenster. Om dit te demonstreren wordt wederom de vraag gesteld wat de totale loonkosten zijn per afdeling. Allereerst wordt de tabel, bijvoorbeeld Medewerkers, toegevoegd aan het queryvenster. Druk vervolgens op de werkbalk op de knop Totalen: Hiermee wordt aangegeven dat er een GROUP BY-constructie opgebouwd gaat worden. In het queryraster komt er een regel bij onder dezelfde naam ‘Totalen’. In deze regel staat vervolgens onder elk gekozen veld de term Group By. Dit moet op de juiste wijze worden ingevuld. Het attribuut Afdeling wordt al gegroepeerd zoals in de regel Totaal te zien is. Het attribuut Basissalaris zal nog de Som-functie moeten krijgen door op de Totaalregel binnen het attribuut Basissalaris te klikken en de functie som te selecteren, zoals in figuur 4.1 te zien is.

34


Figuur 4.1

4.1

Group By met behulp van Queryvenster

Opdracht Voer deze bewerkingen uit zodat figuur 4.1 ontstaat. Het bijbehorende resultaat wordt zichtbaar door te schakelen naar gegevensbladweergave:


SomVanbasissalaris € 41.000,00 € 81.000,00 € 45.000,00 € 89.000,00 € 436.000,00

Access vertaalt de query naar een SQL-commando dat zichtbaar wordt door met de (linker)knop queryweergave te schakelen naar SQL-weergave: → SELECT medewerkers.afdeling, Sum(medewerkers.basissalaris) AS SomVanbasissalaris FROM medewerkers GROUP BY medewerkers.afdeling; In dit commando wordt ten overvloede bij elk vermeld attribuut ook de bijbehorende tabelnaam gegeven. Het gesommeerde bedrag wordt tevens opgeslagen in een variabele genaamd SomVanbasissalaris.

4.2

4.3

4.4

4.5 4.6 4.7

Opdrachten Maak een overzicht waaruit we per klant kunnen aflezen wat het aantal verkooporders is dat de diverse klanten in de eerste helft van 2002 hebben geplaatst. Toon van deze klanten de klantennummers. Noteer het SQL-commando. Maak een lijst met twee kolommen. De eerste kolom vermeldt het klantennummer, de tweede kolom het totale orderbedrag van de verkopen aan deze klant. Sorteer het overzicht zodanig dat de beste klant bovenaan staat. Noteer het SQL-commando. Maak een overzicht waarin per stad het aantal medewerkers wordt weergegeven. Tip: voor deze opdracht tel je het aantal medewerkers per stad. Voor het tellen gebruikt je COUNT(*). Noteer het SQL-commando. Het bedrijf BitByBit kent rustige en drukke weken. Maak een overzicht van de totale orderbedragen per week. Sorteer het overzicht. Noteer het SQL-commando. Maak een lijst waarop voor elk product het totaal aantal stuks staat vermeld dat van dit product werd verkocht. Gebruik het queryvenster. Maak een lijst met twee kolommen. De eerste kolom vermeldt de naam van de klant. De tweede kolom het totale orderbedrag van de verkopen aan deze klant. Sorteer het overzicht zodanig dat de beste klant bovenaan staat. Tip: Raadpleeg twee bestanden en gebruik de JOIN. Gebruik het queryvenster.


35

4.3

Selecties op groepen: HAVING In de vorige paragraaf werd een overzicht gemaakt van het aantal klanten in elke stad. Stel nu dat een medewerker een overzicht wil maken van alle steden waar meer dan twee klanten zijn gevestigd, dan wordt het SQL-commando: → SELECT plaats, COUNT(*) FROM klanten GROUP BY plaats HAVING COUNT(*)>2;

Plaats Landsmeer Sappemeer

Expr1 3 17

Met behulp van de component HAVING worden, nadat de rijen zijn gegroepeerd op plaatsnaam, alleen die plaatsen geselecteerd waar meer dan twee klanten zijn gevestigd. De HAVING-toevoeging kan alleen maar voorkomen in combinatie met een GROUP BY.

4.4

Gebruik queryvenster bij HAVING Een soortgelijke constructie kan op eenvoudige wijze worden opgebouwd via het queryvenster. Eerst wordt de juiste tabel toegevoegd. In de werkbalk wordt weer op de knop Totalen gedrukt: Het veld ‘plaats’ wordt in de eerste kolom gezet, volgens figuur 4.2. Figuur 4.2

Stap 1: de Group By

In de tweede kolom wordt de expressie COUNT(*) ingetypt. In de regel Totaal kies je voor Expressie. In de regel criteria wordt gekozen voor: >2. Zie figuur 4.3. Figuur 4.3

4.8 4.9 4.10

36

Stap 2: toevoegen Having inclusie criterium

Opdrachten Maak een overzicht van de topweken: alle weken waarin meer dan 50.000 euro aan omzet werd behaald. Noteer het SQL-commando. Maak een overzicht van ‘eenmalige klanten’: zij die slechts één order hebben geplaatst. Toon van die klanten de klantnummers. Maak een soortgelijk overzicht: klanten die slechts één order hebben geplaatst. Toon van die klanten de namen.


4.11 4.12 4.13

4.14

Maak een overzicht van de afdelingen waar maandelijks meer dan 5.000 euro in totaal aan loonkosten wordt uitbetaald (uitgaande van het basissalaris). Van welke producten zijn er meer dan tien stuks verkocht? Toon van deze producten het productnummer en het totaal aantal dat verkocht is. Maak een lijst waarop de namen, de adressen en de vestigingsplaatsen staan vermeld, met daarbij het totale orderbedrag van de klanten die in totaal voor meer dan 50.000 euro aan orders hebben geplaatst. Van welke producten zijn er in 2002 (!) meer dan vijftig stuks verkocht? Toon van dit product de omschrijving en het totale aantal verkochte producten.


37

Bijlage Database BitByBit Producten (productnr, omschrijving, prijs, voorraad, productsoort) Klanten (klantnr, naam, adres, postcode, plaats, telnr) Medewerkers (mednr, naam, adres, plaats, afdeling, functie, datumin, basissalaris, provisiep) Verkooporders (ordernr, klantnr, mednr, orderdat, weeknr, orderbedrag) Orderregels (ordernr, productnr, aantal) Facturen (factuurnr, ordernr, factuurdat, productsoort, bedrag, betaald) Betalingen (factuurnr, betaaldat, klantnr, bedrag)

38


Spoedcursus SQL met MS-Access

Recommend Documents