Nijmeegse Scholengemeenschap Groenewoud
Miniles gegevensbanken bevragen met SQL In deze miniles gaat het over gegevensbanken of databases. Dit zijn bestanden waarin gegevens kunnen worden opgeslagen. Het is dan van belang dat je op een eenvoudige manier informatie kunt opvragen uit die vaak zeer grote bestanden. Toepassingen zijn er bij banken en verzekeringsmaatschappijen maar ook de leerling-administratie van de NSG is in een database opgeslagen. Als je daar gegevens uit wilt ophalen kun je dat doe met behulp van een zoekvraag of een query. Als je op Google iets opzoekt wordt er ook een query gemaakt en uitgevoerd en als het goed is krijg je binnen een fractie van een seconde een hele berg websites aangedragen waarin je de door jou gevraagde informatie kunt vinden. De zoekvragen worden gesteld in een daarvoor ontwikkelde zoektaal: SQL (Structured Query Language) Bij deze miniles gebruiken we het programma AccSQL dat je op de informaticawebsite kunt vinden onder het tabblad LINKS en de basishandleiding SQL Een database bestaat uit één of meer tabellen. Een gegeven in zo’n tabel noemen we een record. Elk record bestaat uit meerdere al dan niet ingevulde velden. Een voorbeeld van zo’n tabel is een tabel met persoonsgegevens. Tabel: persoonsgegevens voornaam Jan Piet Jasper Malou Janine Joop Adri
achternaam Janssen Paaltjens Vaandrik Mertens Groothuis Doedelmans Aardmans
geslacht M M M V V M M
adres Kalverstraat 49 Mosterdlaan 123 Modelstraat 23 Jadeplein 12 Merellaan 67 Morgenster 23 Eikendreef 98
postcode 1123 BS 8765 AK 6543 HG 6521 KW 6598 BH 5647 KJ 6581 JK
plaats Amsterdam Rotterdam Nijmegen Nijmegen Groesbeek Arnhem Malden
telefoon 010-1234567 020-7654321 024-8796541 024-9876543 024-3977777 026-1237890 024-3589999
Basishandleiding SQL Een basis zoekvraag bestaat uit de volgende onderdelen: SELECT
, , etc FROM Het SELECT element wordt gebruikt om een of meerdere velden uit een tabel op te vragen. Als er meerdere velden worden bevraagd, dienen deze door een komma te worden gescheiden. Het FROM element wordt gebruikt om aan te geven uit welke tabel of tabellen er gekozen wordt. Als er meerdere tabellen worden bevraagd, dienen deze door een komma te worden gescheiden. Voorbeeld van een zoekvraag: Je wilt van alle personen de voornamen, de achternamen en het adres weten. De query ziet er dan als volgt uit: SELECT voornaam, achternaam, adres FROM persoonsgegevens
Miniles SQL
1
Piet Geelen
Nijmeegse Scholengemeenschap Groenewoud
Dit levert als resultaat op: voornaam Jan Piet Jasper Malou Janine Joop Adri
achternaam Janssen Paaltjens Vaandrik Mertens Groothuis Doedelmans Aardmans
adres Kalverstraat 49 Mosterdlaan 123 Modelstraat 23 Jadeplein 12 Merellaan 67 Morgenster 23 Eikendreef 98
Nu kan het zijn dat je alleen maar personen uit Nijmegen wilt zien in het resultaat. Dan heb je te maken met een voorwaarde, namelijk de personen moeten uit Nijmegen komen. Voor de eerste voorwaarde gebruik je in SQL het WHERE element De zoekvraag kan er dan zo uitzien: SELECT FROM WHERE
voornaam, achternaam, plaats persoonsgegevens plaats = “Nijmegen”
Let op! Als je in een voorwaarde in dit geval de plaats vergelijkt met een serie letters/karakters dan moet je die serie letters tussen dubbele aanhalingstekens plaatsen. Als je iets vergelijkt met een getal, dan mogen die aanhalingstekens niet gebruikt worden. Merk op dat bij de telefoonnummers een streepje is verwerkt. Dat betekent dat dit dan geen getallen zijn! Het resultaat van de query is nu: voornaam Jasper Malou
achternaam Vaandrik Mertens
plaats Nijmegen Nijmegen
Je kunt ook gegevens sorteren; getallen van laag naar hoog en omgekeerd en letters van a tot z (oplopend) en omgekeerd (aflopend). Daarvoor gebruik je het ORDER BY element. Indien je oplopend wilt sorteren kun je de toevoeging ASC gebruiken, bij aflopend sorteren moet je de toevoeging DESC gebruiken. Voorbeeld van een zoekvraag: Stel je wilt de persoonsgegevens allereerst op geslacht sorteren (aflopend) en vervolgens op achternaam (oplopend). Let op! Als je alle velden van een tabel in het resultaat wilt zien hoef je niet alle veldnamen op te schrijven maar kun je gebruik maken van een zogenaamde wildcard. In dit geval het sterretje ( * ) SELECT * FROM persoonsgegevens ORDER BY geslacht DESC, achternaam
Miniles SQL
2
Piet Geelen
Nijmeegse Scholengemeenschap Groenewoud
Het resultaat is dan voornaam Janine Malou Adri Joop Jan Piet Jasper
achternaam Groothuis Mertens Aardmans Doedelmans Janssen Paaltjens Vaandrik
geslacht V V M M M M M
adres Merellaan 67 Jadeplein 12 Eikendreef 98 Morgenster 23 Kalverstraat 49 Mosterdlaan 123 Modelstraat 23
postcode 6598 BH 6521 KW 6581 JK 5647 KJ 1123 BS 8765 AK 6543 HG
plaats Groesbeek Nijmegen Malden Arnhem Amsterdam Rotterdam Nijmegen
telefoon 024-3977777 024-9876543 024-3589999 026-1237890 010-1234567 020-7654321 024-8796541
Het is natuurlijk ook mogelijk om een zoekvraag uit te voeren waar meerdere voorwaarden worden gesteld. We hebben eerder aangegeven dat de eerste voorwaarde met het WHERE element werd gesteld. Elke volgende voorwaarde waaraan een zoekvraag ook moet voldoen wordt met het AND element aangegeven. Voorbeeld van een zoekvraag: Stel je wil gegevens uit de tabel halen waarvoor geldt dat de personen uit Nijmegen moeten komen en bovendien ook nog van het vrouwelijke geslacht dienen te zijn. De query ziet er dan als volgt uit: SELECT FROM WHERE AND
* persoonsgegevens plaats = “Nijmegen” geslacht = “V”
Het resultaat is dan voornaam Malou
achternaam Mertens
geslacht V
adres Jadeplein 12
postcode 6521 KW
plaats Nijmegen
telefoon 024-9876543
Het kan ook voorkomen dat je een zoekvraag stelt waarin je aangeeft dat je een keuze hebt uit 2 of meer voorvaarden. In dat geval wordt voor de tweede en elke volgende voorwaarde het OR element gebruikt. Voorbeeld van een zoekvraag: Stel je wil gegevens uit de tabel halen waarvoor geldt dat de personen uit Malden of uit Groesbeek moeten komen. Het resultaat moet oplopend gesorteerd worden op plaats. De query ziet er dan als volgt uit: SELECT FROM WHERE OR
* persoonsgegevens plaats = “Malden” plaats = “Groesbeek”
Het resultaat is dan voornaam Janine Adri
Miniles SQL
achternaam Groothuis Aardmans
geslacht V M
adres Merellaan 67 Eikendreef 98
3
postcode 6598 BH 6581 JK
plaats Groesbeek Malden
telefoon 024-3977777 024-3589999
Piet Geelen
Nijmeegse Scholengemeenschap Groenewoud
Het kan zijn dat bepaalde velden geen informatie bevat. Dat veld is dus leeg. Om dat te achterhalen maak je gebruik van het IS NULL element. Voorbeeld van een zoekvraag: Stel je wil weten of van iemand de postcode onbekend is. De query ziet er dan als volgt uit: SELECT FROM WHERE
* persoonsgegevens postcode IS NULL
In ons geval zal de resultaattabel bestaan uit 0 records omdat van iedereen de postcode is ingevuld.
Stel je bent op zoek naar gegevens van een persoon uit een database, maar je weet niet hoe die persoon precies heet. Je weet wel dat de achternaam met een “V” begon. Hoe los je dat dan op? Je kunt dan gebruik maken van de wildcard “%” en het LIKE element. De query ziet er dan als volgt uit: SELECT FROM WHERE
* persoonsgegevens achternaam LIKE “V%”
Het resultaat is dan voornaam Jasper
Miniles SQL
achternaam Vaandrik
geslacht M
adres Modelstraat 23
4
postcode 6543 HG
plaats Nijmegen
telefoon 024-8796541
Piet Geelen
Nijmeegse Scholengemeenschap Groenewoud
Keuze van de database 1. 2. 3. 4. 5.
Ga naar de website van informatica ( http://www.groenewoud.nl/subsites/informatica ) Kies het tabblad Links Kijk onder het kopje Minilessen Daarin staat de database leerlingen Sla die op in je map op de computer
Het werken met AccSQL 1. 2. 3. 4. 5. 6.
Ga naar de website van informatica ( http://www.groenewoud.nl/subsites/informatica ) Kies het tabblad Links Kijk onder het kopje Minilessen Daarin staat een link naar AccSQL. Klik op de link en kies 2 maal voor uitvoeren Het programma wordt geopend. • • • • • • • • •
Klik op het menu-item Bestand Kies voor Openen Selecteer de database leerlingen die je daarvoor hebt opgeslagen Ga nu naar het menu-item Database Kies voor tabellen. Je ziet nu bovenaan een map Leerlingen staan Dubbelklik daarop en de gegevens van de tabel worden zichtbaar. Ga weer naar het menu-item Database en kies nu voor SQL Je krijgt nu een venster te zien waarin je de queries kunt maken en uitvoeren. Aan de linkerkant zie je nu dat je een keuze hebt tussen het tabelvenster en het SQL-venster.
Opdrachten Je mag bij alle opdrachten in het SELECT element het * gebruiken. 1. 2. 3. 4.
Welke leerlingen komen uit Utrecht? Sorteer de records aflopend op achternaam Welke meisjes komen er uit Houten? Sorteer ze oplopend op klas. Welke leerlingen hebben geen telefoon? Welke leerlingen wonen in Utrecht in een straat waarin in ieder geval 2 a’s achter elkaar voorkomen? 5. Welke leerlingen wonen in Bunnik of zitten in klas 4a? 6. Welke leerlingen hebben een postcode die begint met 35?
Miniles SQL
5
Piet Geelen
