Hoofdstuk Databases

Hoofdstuk Databases

A Inleiding

Vooraf

In de module Informatieverwerkende systemen hebben we uitgelegd wat we verstaan onder gegevens. Gegevens zijn (combinaties van) tekens of symbolen die niet voor iedereen betekenis hebben. Gegevens worden ook wel data genoemd.

Voor informatica zijn data en de opslag van data belangrijke onderwerpen. Data kunnen gestructureerd of ongestructureerd zijn.

Bijlagen

Bij deze module horen de volgende bijlagen:


Voor het uitvoeren van queries met Microsoft Access:


SQL-tool databases:

CD-DVD database
Transacties

Zo werkt het

Je bent begonnen in de module Databases. Deze module bestaat uit meerdere onderdelen. In ieder onderdeel vind je, verdeeld over verschillende pagina's, informatie in de vorm van teksten, afbeeldingen en video's. Daarnaast ga je zelf aan de slag. Onder het kopje "Aan de slag" vind je steeds toepassingsopdrachten. Deze opdrachten maak je alleen of met een klasgenoot.

Er zijn ook oefeningen. Deze herken je aan de blauwe knop met daarop "Oefening". Een oefening bestaat uit meerdere vragen. Dat kunnen gesloten vragen zijn, die door de computer worden nagekeken, of open vragen, die je zelf moet nakijken. Bij een enkele vraag moet je een bestand uploaden.

B Data

Gestructureerde data

Van gestructureerde data is sprake als data op een standaard manier worden beschreven en de relaties die er bestaan tussen de data helder zijn. Hoe doen we dat? Lees daarvoor de volgende teksten.

Rijen en kolommen

Een manier om data te structureren, is door deze onder te brengen in rijen en kolommen. Een voorbeeld is een tabel van een bedrijf waarin horizontaal de naam, adres en woonplaats  zijn weergegeven. Dit voorbeeld zie je hieronder staan.

 




 

Ongestructureerde data

Data kunnen ook ongestructureerd zijn. Een twitterbericht of een foto waar verder geen informatie over wordt gegeven, zijn voorbeelden van ongestructureerde data.

Tegenwoordig bestaat het grootste deel van alle gegevens uit ongestructureerde data. Zelfs meer dan 90%. Ga maar eens na wat je zelf opslaat aan bestanden, foto’s en films. Alleen al de opslag van deze grote hoeveelheden ongestructureerde data is een grote uitdaging voor de toekomst. En dan hebben we het nog niet gehad over het analyseren van dit soort gegevens.

Vaak willen we ook met ongestructureerde data meer doen dan alleen maar lezen of bekijken. Zijn er patronen of trends te herkennen in deze data? Of zijn we in staat regelmatigheden in deze grote hoeveelheden ongestructureerde data te ontdekken (dit wordt vaak Big Data genoemd)?

C Dataplatformen

DBMS

Voor het beheer van grote verzamelingen gestructureerde gegevens worden aparte systemen gebruikt. Denk bijvoorbeeld aan de gegevens van financiële instellingen, van scholen, van de NS of van bibliotheken. Zo'n verzameling gestructureerde gegevens noemen we een database. Op een database kunnen bewerkingen worden uitgevoerd zoals het toevoegen, veranderen of verwijderen van gegevens, maar vooral het raadplegen van gegevens. Het programma dat deze bewerkingen uitvoert, wordt een database management system (DBMS) genoemd.

In 1970 kwam de wiskundige E.F.Codd met een destijds radicaal nieuw idee voor de opslag van data: het relationele model. In dit model bestaat een database uit één of meerdere tabellen, die vervolgens weer opgebouwd zijn uit rijen en kolommen. De term relationeel komt voort uit het feit dat er een relatie kan worden gelegd tussen de gegevens in de kolommen in de gebruikte tabellen. Een database management system (DBMS) dat is gebaseerd op het model van E.F.Codd noemen we een relationeel database management system (RDBMS).

★ Aan de slag 1

Als je de onderstaande afbeelding bekijkt zie je een tabel waarin de gegevens van leerlingen worden bijgehouden. In de eerste rij van de tabel, de 'heading', staan de namen van de kolommen: de veldnamen. Alle cellen / velden van één kolom behoren tot hetzelfde type. Uitgezonderd de eerste rij bevat elke tabelrij een bij elkaar horende groep gegevens, die verdeeld zijn over de kolommen van een tabel. In het onderstaande voorbeeld zijn dat de gegevens van één leerling.

Bekijk de tabel en lees de toelichting bij de verschillende onderdelen.


★ Aan de slag 2

Bekijk de tabel en beantwoord de volgende vragen:

 

  1. Hoeveel veldnamen kom je tegen in deze tabel?
  2. Vertaal de derde rij in de tabel naar een normale Nederlandse zin.
  3. Op welke veldnaam is de tabel gesorteerd?
  4. Uit hoeveel records bestaat de tabel?
    Hoeveel velden heeft ieder record?
  5. Er is één kolom waar ieder gegeven maximaal één keer voorkomt.
    Welke kolom is dat?
  6. Waarom krijgt iedere leerling een uniek administratienummer?

 

★ Aan de slag 3

Beantwoord de volgende vragen:

  1. Van welk type zijn de gegevens in de eerste kolom?
  2. En in de kolom adres?
  3. Deze tabel is nog niet compleet.
    Welke kolommen zouden er volgens jou nog moeten worden toegevoegd?

Administratiesysteem

In de schooladministratie wordt gebruik gemaakt van een administratienummer om een leerling uniek te kunnen identificeren. Een kolom (of een combinatie van kolommen) op basis waarvan een rij uit een tabel te identificeren is, noemen we de unieke sleutel. In een schooladministratiesysteem kunnen veel gegevens bijgehouden worden. Stel dat de schoolleiding wil bijhouden wanneer een leerling absent is. Hiervoor zou de tabel uitgebreid kunnen worden met de extra kolom 'absentie'.

 

Als je de tabel bekijkt, dan zie je dat dit niet de ideale oplossing is. Om alle absenties te kunnen vastleggen zijn in deze tabel dezelfde gegevens meerdere keren opgenomen. Wanneer dezelfde informatie vaker in een tabel is opgenomen, is er sprake van redundantie. In sommige gevallen kan redundantie nuttig of noodzakelijk zijn, maar over het algemeen kleven er nadelen en gevaren aan.

Stel dat Robert-Jan Velthausz verhuist naar de Kennedylaan 245. Op de administratie moet het adres aangepast worden. In de tabel komt het adres van Robert-Jan meerdere malen voor. Het adres moet in dit geval dan op drie plaatsen gewijzigd worden. Dat is niet alleen onhandig, maar er schuilt ook het gevaar in dat het op één plaats vergeten wordt.

Wanneer dat gebeurt, komt er tegenstrijdige informatie in de database te staan. We spreken dan van inconsistentie. Het gevaar van redundantie is dat deze kan leiden tot inconsistentie

Een ander probleem is dat er dan een nieuwe sleutel gezocht moet worden. Het 'admnr' kan geen sleutel meer zijn, omdat er meerdere rijen in de tabel met hetzelfde administratienummer voorkomen. Een goede sleutel is in dit geval de combinatie 'admnr' en 'absentie'.



Het is mogelijk het ontwerp aan te passen zodat redundantie verdwijnt en deze vorm van inconsistentie onmogelijk wordt. De oplossing ligt in het maken van twee tabellen.

Bij het ontwerpen van een relationele database is het belangrijk om goed na te denken over de samenstelling van de tabellen. Wanneer dit eenmaal gebeurd is, kun je met een paar handelingen de informatie die erin zit goed tevoorschijn halen.

Niet alleen kun je met behulp van deze twee tabellen een lijst samenstellen met de namen van de leerlingen die absent zijn geweest, maar je kunt ook vragen beantwoorden als: 'In welke klas is nog nooit iemand absent geweest?' of 'Uit welke plaatsen komen de absenten?'.

Om met een relationele database te kunnen werken, heb je een database management system nodig. De populaire DBMS-programma's bevatten als kern allemaal dezelfde vraagtaal: SQL. In deze module maken we kennis met deze programmeertaal.

★ Aan de slag 4

In de tabel leerlingen is admnr de unieke sleutel waarmee iedere leerling geïdentificeerd wordt.
 

  1. Waar dient een unieke sleutel voor?
  2. Men had er ook voor kunnen kiezen om de combinatie voornaam, achternaam, adres en woonplaats als unieke sleutel te gebruiken. Waarom wordt dat niet gedaan?

 

★ Aan de slag 5

In het dagelijks leven is er ook vaak sprake van een identificatie door middel van een unieke sleutel. Denk daarbij bijvoorbeeld aan de diverse overheidsdiensten.
 

  1. Geef drie voorbeelden.
  2. Auto's kunnen op twee manieren geïdentificeerd worden, dus met twee verschillende unieke sleutels. Welke sleutels zijn dat en door wie of welke instantie worden deze gebruikt?
  3. Met welke sleutel worden bromfietsen geïdentificeerd?

★ Aan de slag 6

Om alle absenties te kunnen vastleggen, zijn in de eerste tabel (bij het onderdeel Administratiesysteem) dezelfde gegevens meerdere keren opgenomen.

  1. Waarvan is er sprake als dezelfde informatie meer dan één keer in een tabel staat?
  2. Wat is daarvan een gevaar?
  3. Geef een mogelijke oplossing.

 

★ Aan de slag 7

Bekijk de de onderstaande twee tabellen.

Tabel 1

 

Tabel 2

 

  1. Wat zijn de voor-en achternaam van de leerling die op 13 november 2016 afwezig was?
  2. Welke leerling was drie dagen afwezig? Wat heb je gedaan om hierachter te komen?
  3. Wat is de unieke sleutel van de tabel absentie (dit kan één of een combinatie van meerdere kolommen zijn)?

 

★ Aan de slag 8

Wanneer dezelfde informatie meer dan één keer in een tabel staat, is er sprake van redundantie.

 
 
  1. Zoek op internet de betekenis op van het begrip redundantie.
  2. In welke klas zijn geen absenten geweest?

 

★ Aan de slag 9

Het doel van een unieke sleutel is dat je ermee iets kan identificeren.

  1. Zoek op internet wat er wordt bedoeld met identificeren.
  2. Is de pincode van jouw bank- of giropas een unieke sleutel?
    Waarom wel of waarom niet?

D Database transacties

Inleiding

In deze module gaan we twee relationele databases gebruiken:

  1. De database transacties
  2. De cd-dvd database

De database transacties bevat gegevens over de financiële transacties van de ING bank. De cd-dvd database bevat de gegevens over cd's en dvd's die kunnen worden geleend.

Bijna alle door ons gebruikte gegevens zijn fictief. In de tabel bank staan de BIC's (Bank Identificatie Codes) van banken die in het echt ook gebruikt worden. Alle rekeningnummers, namen en adressen van de klanten zijn echter verzonnen. Dit geldt ook voor de NAW-gegevens die gebruikt worden in de cd-dvd database.

Bij de opdrachten wordt steeds aangegeven welke database je moet gebruiken.

Tabellen

De database transacties bestaat uit 5 tabellen:

  • De tabel bank
  • De tabel rekening
  • De tabel rekeninghouder
  • De tabel transactie
  • De tabel betaalpas

 

Tabel bank

In de tabel bank staan de namen van de Nederlandse banken. Elke bank wordt geïdentificeerd door middel van een Bank Identificatie Code (BIC) . De BIC is een unieke code die elke bank gebruikt voor internationale betalingen. Voor de Rabobank is dat bijvoorbeeld RABONL2U.

Tabel rekening

In de tabel rekening staan de gegevens van de rekeninghdr_id, de bank_id, het saldo en de status van de rekening. Elke rekening wordt geïdentificeerd door middel van de rekening_id.

Een rekening kan op naam staan van één persoon of meerdere personen. In dat laatste geval hebben we te maken met zogenaamde 'en/of' rekeningen. Voor een 'en/of' rekening kunnen meerdere betaalpassen zijn uitgegeven.

 
 

 

Elke rekening heeft een daarnaast een status. De status van een rekening kan zijn:

  • actief
  • geblokkeerd
  • opgeheven

 

Wanneer een persoon of bedrijf failliet is, wordt de rekening geblokkeerd. Op de rekening kunnen dan nog wel bedragen worden bijgeschreven, maar er kan niets meer worden afgeschreven.

Tabel rekeninghouder

In de tabel rekeninghouder staan de naam-, adres en woonplaatsgegevens van de rekeninghouders.



Tabel transactie

In de tabel transactie staat de datum waarop de transactie plaatsvond, de kolom rekening_bij, de kolom rekening_af, een omschrijving, het type transactie, voor overschrijvingen de betaalstatus van de overschrijving en tot slot het bedrag. Elke transactie wordt geïdentificeerd door middel van een transactie_id.



 

Een transactie is van een bepaald type:

  • AC: acceptgiro
  • BA: betaalautomaat
  • GM: geldautomaat
  • OV: overschrijving
  • ST: storting
  • PO: periodieke overschrijving

 

De betaalstatus van een overschrijving kan zijn:

  • goedgekeurd - code GK - (de betaalopdracht is uitgevoerd)
  • afgekeurd - code - AK - (de betaalopdracht is definitief afgewezen, bijvoorbeeld vanwege een te laag saldo)
  • gereserveerd - code GR - (komt voor bij spoedbetalingen. Deze worden op werkdagen tot 16:30 uur verwerkt. Als de betaling na dit tijdstip wordt gedaan, krijgt de betaling tot 08:00 uur de volgende dag de status 'gereserveerd').

 

Alle type transacties

Een transactie is van een bepaald type:

  • AC: acceptgiro
  • BA: betaalautomaat
  • GM: geldautomaat
  • OV: overschrijving
  • ST: storting
  • PO: periodieke overschrijving

Tabel betaalpas

Op de betaalpas staan het pasnummer en het rekening_id. De combinatie pasnummer en rekening_id is uniek. In het geval van een 'en/of' rekening kunnen er meerdere passen gekoppeld zijn aan hetzelfde rekening_id.



Relaties

De tabellen in de database transacties zijn onderling met elkaar verbonden. Met andere woorden: er bestaan relaties tussen de diverse tabellen.

Om de tabellenstructuur van een database inzichtelijk te maken, wordt vaak een strokendiagram gebruikt. De tabellen verschijnen als stroken met daarboven de namen van de kolommen. De sleutel van een tabel wordt aangegeven met een dubbele pijl onder de kolom of kolommen die deel uit maken van de sleutel. Indien een sleutel betrekking heeft op meerdere kolommen, noemen we dat een samengestelde sleutel.

 

 

Het strokendiagram van de database transacties kun je als pdf downloaden onder het menu-item Bijlagen (zie Inleiding) en op IT's Learning. Je hebt dit strokendiagram nodig bij het maken van opdrachten die uitgevoerd worden met deze database.

E SQL

Inleiding

SQL, de afkorting staat voor Structured Query Language, is een formele taal die speciaal ontworpen is voor de communicatie met databases. SQL is de standaardtaal die tegenwoordig door de meeste databasesystemen wordt gebruikt.

In de vraagtaal SQL kunnen opdrachten worden gegeven waarmee gegevens uit een database kunnen worden opgevraagd, worden toegevoegd, worden gewijzigd of worden verwijderd.

Een SQL-opdracht wordt een query genoemd. Het Engelse woordje 'query' betekent (zoek-)vraag.

★ Aan de slag 11

Om de tabellenstructuur van een database weer te geven, wordt vaak een strokendiagram gebruikt. Bekijk bijvoorbeeld het strokendiagram van een webwinkel.


Beantwoord de volgende vragen:

  1. Hoe wordt de unieke sleutel van een tabel aangeduid in het strokendiagram?
  2. Noteer de drie namen van de tabellen van de database webwinkel en geef aan wat de bijbehorende unieke sleutels zijn.
  3. Komen in dit strokendiagram samengestelde unieke sleutels voor?

 

★ Aan de slag 12

Tussen de verschillende tabellen in een relationele database bestaan verbanden.

  1. Hoe worden de verbanden tussen de tabellen in een strokendiagram aangegeven?
  2. Schrijf alle verbanden op die er zijn in de database van de webwinkel.

SQL

We beginnen met een eenvoudige zoekopdracht. Stel dat de eigenaar van de webwinkel een lijst van de namen van al zijn klanten wil hebben. Deze vraag kan in SQL als volgt geformuleerd worden:

SELECT achternaam, voornaam
FROM klant;

Achter SELECT staan de kolommen die getoond moeten worden. De kolomnamen worden gescheiden door komma's. Achter FROM staat de naam van de tabel (of tabellen) waaruit deze kolommen komen. Een SQL-vraag wordt altijd afgesloten met een puntkomma (;). Bij het programma Adminer kan je puntkomma weglaten.

Wanneer de eigenaar van de webwinkel alle gegevens uit de tabel klant wil hebben dan zou de vraag er zo uit kunnen zien:

SELECT klant_id, achternaam, voornaam, adres, woonplaats
FROM klant;

 



​Dit kan echter korter. Wanneer je alle kolommen wilt zien dan ziet de SQL-vraag er zo uit:

SELECT *
FROM klant;

De asterisk (*) zorgt ervoor dat alle kolommen op het scherm worden getoond.

★ Aan de slag 14

Bekijk de onderstaande query uit de webwinkeldatabase:

SELECT datum, tijd
FROM bestelling;

 

  1. Beschrijf in eigen woorden wat hier wordt bedoeld.
  2. Je ziet dat de SQL-commando's met hoofdletters worden getypt. Dat is niet verplicht, maar waarom zou dit vaak wel worden gedaan?

★ Aan de slag 15

Bekijk de onderstaande tabel:

 

  1. Welke query levert als resultaat de namen en omschrijvingen van alle producten op?
  2. Met welke query krijg je alle kolommen uit de tabel product te zien?

 

★ Aan de slag 16

Open de SQL-tool.

Transacties

 

We maken voor deze opdracht gebruik van de database transacties.

Beantwoorde de volgende vragen door de juiste zoekopdracht/query op te schrijven.

Bedenk dat bij alle vragen het steeds gaat om de correct zoekopdracht/query en dat de uiteindelijke uitkomsttabel niet belangrijk is. Deze moet namelijk goed zijn als de query ook goed is.

 

  1. Maak een lijst met de namen, het adres en de woonplaats van de rekeninghouders.
  2. Maak een lijst met alle gegevens van de rekeninghouders.

WHERE-statement

Een selectie maken
Je hebt gezien dat je met behulp van SQL een volledige tabel of een aantal kolommen uit een tabel op het scherm kunt tonen. Het zal ook voorkomen dat je niet de gegevens uit alle rijen wilt hebben.

Stel dat je een lijst wilt hebben van de namen van alle rekeninghouders die in Utrecht wonen. In dat geval zal er een selectie van rijen moeten worden gemaakt. Dit doe je door een voorwaarde te stellen:

SELECT rekeninghouder
FROM rekeninghouder
WHERE plaats = 'Utrecht';

Achter het where-statement staat de voorwaarde "plaats = 'Utrecht' ". Hiermee wordt bedoeld dat alleen die rekeningshouders moeten worden getoond die in de plaats Utrecht wonen.


In de volgende query wordt een lijst gemaakt van het rekening_id, het saldo en de status van de rekeningen waarbij het saldo groter is dan, of gelijk aan, € 1900,00.

SELECT rekening_id, saldo, status
FROM rekening
WHERE saldo >= 1900.00;

Kijk eens naar de notatie van het bedrag. In plaats van 1900,00 hebben we in de query 1900.00 genoteerd. Als decimaalteken hebben we een punt in plaats van een komma gebruikt. In Nederland en België zetten we een komma voor decimalen. Bijvoorbeeld 24,95. In Engelstalige landen wordt als decimaalteken een punt gebruikt en is de notatie dus 24.95 in plaats van 24,95.

SQL kent een aantal verschillende type data zoals tekst en getallen. Bij het maken van een tabel moet voor iedere kolom een geschikt datatype worden gekozen. Als het om tekst gaat, dan moet de waarde tussen aanhalingstekens staan. Is er sprake van een numeriek gegevens (getallen) dan moet je de aanhalingstekens weglaten. Een datum wordt in SQL tussen aanhalingstekens gezet.

De volgende query levert als resultaat een lijst op met de transactie_id, de datum en het bedrag. Alleen die rijen worden getoond, waarvan de datum na 19-12-2015 ligt.

SELECT transactie_id, datum, bedrag
FROM transactie
WHERE datum > '19-12-2015';

De Europese notatie voor een datum is eerst de dag, dan de maand en daarna het jaar. In de Verenigde Staten is dat eerst de maand, dan de dag en daarna het jaar. In de SQL-tool wordt de datum in de tabel transacties nog anders genoteerd. Namelijk eerst het jaar, daarna de maand en dan de dag.


In onze SQL-tool wordt de query dus:

SELECT transactie_id, datum, bedrag
FROM transactie
WHERE datum > '2015-12-19';


Een query in SQL heeft altijd de volgende basisstructuur:

SELECT <een of meer kolommen>
FROM   <een of meer tabellen>
WHERE  <een of meer voorwaarden>;

 

★ Aan de slag 17

Open de SQL-tool. We maken voor deze opdracht weer gebruik van de database transacties.

Transacties

 

 

a.  Geef alle rekeninghouders die uit Amsterdam komen.

b. Wat zijn alle gegevens van de transacties op 13 januari 2016.

Sorteren

De query heeft geen invloed op de volgorde waarin de rijen getoond worden. In SQL kan het resultaat gesorteerd worden met behulp van het statement ORDER BY.

Wanneer we een overzicht willen hebben van het adres en de woonplaats van alle rekeninghouders die wonen in Alkmaar en dat gesorteerd op adres, dan kunnen we dat doen met de volgende query:

SELECT adres, plaats
FROM rekeninghouder
WHERE plaats = 'Alkmaar'
ORDER BY adres;

In dit voorbeeld wordt de tabel oplopend gesorteerd. Het is ook mogelijk om aflopend te sorteren. De Engelse term daarvoor is 'descending' (DESC). Wanneer je bij het ORDER BY statement niets vermeld, wordt er standaard oplopend gesorteerd. Wil je dat het resultaat aflopend wordt gesorteerd dan zal je achter de kolom DESC moet vermelden.

Een voorbeeld

SELECT adres, plaats
FROM rekeninghouder
WHERE plaats = 'Alkmaar'
ORDER BY adres DESC;

 

Sorteren op meerdere kolommen
Wanneer je een overzicht wilt hebben van alle opgenomen of afgeschreven bedragen en de data waarop de transacties hebben plaatsgevonden en je wilt sorteren op bedrag, kan het een probleem worden als sommige bedragen meerdere keren voorkomen. In dat geval kun je de tabel op twee kolommen sorteren. In het resultaat willen we alleen bedragen die hoger zijn dan € 500,00 terugzien.

SELECT transactie_id, datum, bedrag
FROM transactie
WHERE bedrag > 500
ORDER BY bedrag, datum;

 

 

★ Aan de slag 18

 

  1. Welk commando gebruik je voor het maken van selecties?
  2. Hoe wordt de volgende query gesorteerd?

    SELECT type, bedrag
    FROM transactie
    WHERE type = 'BA'
    ORDER BY bedrag;

★ Aan de slag 19

Bekijk de volgende query:

SELECT datum, bedrag
FROM transactie
ORDER BY datum, bedrag;
 

  1. In de laatste regel zie je 'datum' en 'bedrag' staan. Wat is het effect hiervan?
  2. Hoe ziet de query eruit als je eerst sorteert op bedrag en vervolgens op datum?
  3. Wordt de lijst oplopend of aflopend gesorteerd?
  4. Herschrijf de query, zodat de lijst aflopend op beide kolommen wordt gesorteerd.

 

 

Geen duplicaten

We willen dat van alle rekeningen het saldo en de status van de rekening wordt vermeld. We kunnen deze gegevens opvragen met de volgende query:

SELECT saldo, status
FROM rekening;

In de resultaattabel hieronder komen sommige combinaties meerdere keren voor. Dat geldt bijvoorbeeld voor de combinatie saldo 750 en status actief


Als we een lijst willen hebben waarin elke combinatie maar één keer voorkomt, moeten we in SQL de dubbele rijen verwijderen. Daarvoor maken we gebruik van het keyword DISTINCT:

SELECT DISTINCT saldo, status
FROM rekening;
 

★ Aan de slag 20

Bekijk de volgende query:

SELECT DISTINCT plaats
FROM rekeninghouder;

  1. Leg in eigen woorden uit wat het keyword DISTINCT in deze query doet.
  2. Maakt het verschil uit als je in dit voorbeeld het keyword DISTINCT weglaat?
  3. Open de SQL-tool database transacties. Voer de query uit met en zonder het commando DISTINCT en controleer of het antwoord dat je hebt gegeven bij b klopt.

 

Transacties

★ Aan de slag 21

Open de SQL-tool. We maken voor deze opdracht weer gebruik van de database transacties.
 

Transacties


Beantwoord de volgende vragen met behulp van queries.

Je kunt gebruik maken van het strokendiagram database transacties. Dit strokendiagram vind je bij Bijlagen.
 

  1. Maak een lijst van de NAW-gegevens van rekeninghouders die in Heerlen wonen.
  2. Maak een lijst van de rekeningen waarbij het saldo hoger is dan € 750,00.
  3. Wat is het adres van de rekeninghouder die telefoonnummer 010-4553666 heeft?
  4. Maak een lijst van alle typen transacties. In de lijst mogen géén duplicaten voorkomen.
  5. Wat is de BIC-code van de Rabobank?

F Voorwaarden

AND-operator

Tot nu toe heb je queries moeten schrijven waarbij de te selecteren rijen aan één voorwaarde moesten voldoen. Sommige vragen zijn daarmee niet te beantwoorden, bijvoorbeeld de vraag: Wat is het adres van de vrouw die Gerdien Jansen heet en in Alkmaar woont?


SELECT adres, rekeninghouder
FROM rekeninghouder
WHERE plaats = 'Alkmaar';

Dat gaat goed als er maar één rekeninghouder in Alkmaar woont. Maar het ligt voor de hand dat deze query meerdere adressen oplevert zoals we hieronder kunnen zien.
 


Om het achter juiste adres te komen, moet er een tweede voorwaarde gesteld worden: de naam van de rekeninghouder.

SELECT adres, rekeninghouder
FROM rekeninghouder
WHERE plaats = 'Alkmaar'
AND rekeninghouder = 'Gerdien Jansen';

Alleen die rijen moeten worden geselecteerd waarvoor geldt dat beide voorwaarden waar zijn. Hiervoor gebruik je de AND-operator.

OR-operator

De ING-bank wil een overzicht van de transacties die de status goedgekeurd of afgekeurd hebben gekregen. Daarvoor wordt de volgende query uitgevoerd:

SELECT transactie_id
FROM transactie
WHERE status_ov = 'GK'
OR status_ov = 'AK';
 

 

★ Aan de slag 22

Had je in de query bij de vorige paragraaf in plaats van de OR-operator ook de AND-operator mogen gebruiken? Licht je antwoord toe.

LIKE-operator

De OR-operator zorgt ervoor dat de rijen worden geselecteerd waarvoor geldt dat één van beide of beiden voorwaarden waar zijn. De ING-bank had ook een andere query kunnen maken, die hetzelfde resultaat oplevert:

SELECT transactie_id
FROM transactie
WHERE status_ov LIKE '_K'

Met de LIKE-operator kun je die rijen selecteren waarbij de waarde in de kolom aan een bepaald patroon voldoen. Achter de LIKE-operator staat '_K'. De letter K moet precies kloppen, maar de underscore heeft voor de LIKE-operator een speciale betekenis: de underscore staat in plaats van een enkel teken en daar kan dus ieder teken staan. In plaats van een underscore kan ook het procentteken (%) gebruikt worden. Dit teken staat voor geen, één of meer tekens.

SELECT adres
FROM rekeninghouder
WHERE adres LIKE 'Ad%'

Het resultaat van deze query is:

 

Let op:
Als je in een query gebruik wilt maken van een van de twee wildcards (  _ en %  ) dan kan dat alleen in combinatie met de LIKE-operator!

 

Samengevat

  • De operator _ staat voor één enkel willekeurig teken.
  • De operator % staat voor geen, één of meerdere willekeurige tekens.

 

★ Aan de slag 23

Om te weten te komen welke rekeninghouder op de Kanaalkade in Amsterdam woont, zou de bank de volgende query kunnen uitvoeren:

SELECT rekeninghouder, adres, plaats
FROM rekeninghouder
WHERE adres LIKE 'Kanaalkade%'

 

  1. Waarom voldoet deze query niet (bestudeer hiervoor de inhoud van de tabel rekeninghouder)?
  2. Pas de query zo aan dat je wel het gewenste resultaat te zien krijgt.

★ Aan de slag 24

Behalve de AND-operator bestaat er ook de OR-operator.
Leg in eigen woorden uit waarvoor je deze operator kunt gebruiken en leg zo goed mogelijk het verschil uit tussen deze twee operatoren.

★ Aan de slag 25

In sommige gevallen kun je in plaats van de OR-operator de LIKE-operator gebruiken. Kijk bijvoorbeeld naar de volgende query:

SELECT bank_id, bank
FROM bank
WHERE bank_id = 'ABNANL2A' OR bank_id = 'BICKNL2A' OR bank_id = 'INGBNL2A';


  1. Herschrijf de query door gebruik te maken van de LIKE-operator.
  2. Wat is bij de LIKE-operator het verschil tussen de wildcards  % en _ ?

 

NOT-operator

De ING-bank wil een overzicht genereren van de namen van alle rekeninghouders met uitzondering van de rekeninghouders die in Utrecht wonen.

SELECT rekeninghouder, plaats
FROM rekeninghouder
WHERE NOT plaats = 'Utrecht';

De NOT-operator zorgt ervoor dat die rijen worden geselecteerd waarvoor geldt dat de voorwaarde niet waar is.

Een andere query, die hetzelfde resultaat oplevert, is:

SELECT rekeninghouder, plaats
FROM rekeninghouder
WHERE plaats != 'Utrecht'

 

 

 

Overzicht operatoren

De relationele operatoren die je bij SQL kan gebruiken zijn:

<   kleiner dan
>   groter dan
<=   kleiner dan of gelijk aan
>=   groter dan of gelijk aan
=   is gelijk aan
!=   is ongelijk aan

★ Aan de slag 26

Van alle rekeninghouders die in Alkmaar wonen, klopt bij één rekeninghouder het telefoonnummer niet. Deze rekeninghouder is onlangs verhuisd van Goes naar Alkmaar. In de database wordt echter nog steeds het oude telefoonnummer vermeld. Het netnummer van Alkmaar is 072 en dat van Goes 0113.

 

  1. Schrijf een query waarbij je met behulp van het netnummer van Alkmaar achterhaalt om wie het gaat.
  2. Schrijf een query waarbij je met behulp van het netnummer van GOES achterhaalt om wie het gaat.

IS (NOT) NULL

In een database kunnen in tabellen sommige velden leeg gelaten worden. In de tabel rekeninghouder staat een kolom telefoonnr. Stel dat een rekeninghouder geen telefoonnummer heeft of er bewust geen heeft doorgegeven aan de bank. In dat geval wordt er geen waarde opgeslagen in het veld telefoonnr.

In de tabel transactie zal bij een geldopname bij een geldautomaat wel een rekeningnummer worden vermeld in de kolom rekening_af, maar niet in de kolom rekening_bij. Dat veld blijft dan leeg

In SQL kan je de rijen waarbij een veld leeg is, selecteren met de operator IS NULL.

Alle rijen waarbij de velden wel een waarde bevatten, kan je selecteren met de operator IS NOT NULL.

 

Opdracht

Schrijf de query waarmee je kunt controleren of alle rekeninghouders een telefoonnummer hebben opgegeven.

★ Aan de slag 27

Bekijk de onderstaande query.

SELECT transactie_id, type
FROM transactie
WHERE rekening_af IS NULL
AND (type = 'GM' OR type ='OV')

Leg uit waarom het voor de hand ligt dat geen enkele rij aan deze voorwaarden voldoet.

★ Aan de slag 28

De ING wil een overzicht van alle transacties van het type GM (geldautomaat) of OV (overschrijving) vanaf 1 maart 2016. Daarvoor is de onderstaande query opgesteld:


SELECT transactie_id, type, rekening_bij, rekening_af, datum
FROM transactie
WHERE datum >= '2016-03-01'
AND type ='GM' OR type = 'OV'

Het resultaat van deze query is een lijst van alle transacties van het type GM vanaf 1 maart 2016 en alle transacties die van het type OV zijn (en dus ook die van voor 1 maart 2016). Dat was niet de bedoeling!


Je kunt deze query vergelijken met de volgende som: 3 + 2 x 5. De uitkomst van deze som is 13. Je vermenigvuldigt namelijk eerst 2 met 5 en daarna tel je er pas 3 bij op. Kortom: vermenigvuldigen gaat vóór optellen. Als het de bedoeling is om eerst 3  en 2 op te tellen en daarna pas te vermenigvuldigen, dan moet je haakjes gebruiken: (3 + 2) x 5.

Hetzelfde is het geval wanneer je in een voorwaarde AND en OR gebruikt: eerst wordt AND uitgevoerd en daarna pas OR.

In het bovenstaande voorbeeld wordt dus eerst gezocht naar de rijen waarvoor geldt dat het transacties zijn vanaf 1 maart 2016 en van het type = 'GM'. Daarna wordt er gezocht naar alle transacties met type = 'OV'. Om het gewenste resultaat te krijgen, moeten er haakjes gebruikt worden in de query.

SELECT transactie_id, type, rekening_bij, rekening_af, datum
FROM transactie
WHERE datum >= '2016-03-01'
AND (type ='GM' OR type = 'OV')


Het resultaat zijn alle transacties van het  type = 'GM' of het type = 'OV' na 1 maart 2016.

★ Aan de slag 29

 

  1. Bekijk de onderstaande query:

    SELECT rekeninghouder, adres, plaats
    FROM rekeninghouder
    WHERE NOT plaats = 'Utrecht'

    Wat selecteert deze query?
  2. Er is een andere manier om hetzelfde resultaat te krijgen. Dan moet je gebruik maken van één van de relationele operatoren van SQL. Herschrijf de de query op die manier.

 

IN-operator

De bank wil een lijst van alle transacties waarvoor zowel bij rekening_bij als bij rekening_af een rekeningnummer moet worden ingevuld.

Bekijk de onderstaande query:

SELECT transactie_id
FROM transactie
WHERE type = 'BA'
OR type = 'OV'
OR type = 'PO'
OR type = 'AC'

Deze query ziet er nogal omslachtig uit.  Met de IN-operator kan deze query vereenvoudigden.

SELECT transactie_id
FROM transactie
WHERE type IN ('BM', 'OV', 'PO', 'AC')

Deze IN-operator zorgt ervoor dat die rijen worden geselecteerd waarbij in de kolom type de waarden BM, OV, PO of AC staat.

 

★ Aan de slag 30

Bekijk de query met de samengestelde voorwaarden:

SELECT transactie_id
FROM transactie
WHERE type = 'BM'
OR type = 'OV'
OR type = 'PO'
OR type = 'AC';

Dat is een omslachtige manier.

  1. Met welke operator kun je deze query vereenvoudigen?
  2. Hoe ziet de query eruit die alle rekeninghouders selecteert die in Amsterdam, Rotterdam en Utrecht wonen.

★ Aan de slag 31

  1. Maak een lijst van alle rekeningen waarbij het saldo € 250,00, € 350,00, € 750,00 of  850,00 is. Maak hierbij gebruik van de IN-operator.
  2. Als de bedragen tussen aanhalingstekens geplaatst worden, levert de query géén records in de resultaattabel op. Waarom niet?

Toets 1

Adaptieve toets

In de volgende toets wordt getoetst wat je inmiddels zou moeten weten over het onderwerp databases.
Maak de toets:

Databases

G Tabellen combineren

Tabellen combineren I

De database transacties bestaat uit meerdere tabellen. Dat is niet zomaar gebeurd. Er zijn goede redenen om de informatie te verspreiden over verschillende tabellen. In de vorige onderdelen heb je geleerd hoe je met behulp van SQL informatie kunt halen uit één tabel. Om een antwoord op een vraag te krijgen, zal het echter vaak nodig zijn om de informatie uit meerdere tabellen te combineren. In een relationele database worden twee tabellen gecombineerd tot een nieuwe tabel door ze te koppelen (in het Engels: join). Als je daarbij niet oppast, kan dat tot vreemde resultaten leiden.

We nemen als voorbeeld de administratie van een buurtvereniging. Die bestaat uit twee tabellen: leden en kinderen.
 

Tabel: Leden

 

Tabel: Kinderen


Om een uitnodiging te maken voor een spelletjesmiddag wil de organisatie een lijst van de roepnamen van de kinderen, hun achternamen en adressen hebben.
In een SQL-query geef je bij FROM aan welke tabellen geraadpleegd moeten worden. Als je meer dan één tabel opgeeft, worden de tabellen eerst gecombineerd tot één tabel. Daarna wordt die combinatietabel gebruikt om de gewenste informatie te vinden.
De volgende query heeft als resultaat een combinatietabel:

SELECT roepnaam, naam, adres,
FROM leden, kinderen

Het resultaat van deze query zal niet de lijst opleveren die de organisatie had beoogd:

Resultaat


In deze lijst zijn alle mogelijke combinaties van rijen uit de twee tabellen afgedrukt: ieder kind wordt drie keer afgedrukt: een keer met zijn eigen achternaam en adres en daarnaast met de namen en adressen van de andere leden. Dit levert dus 12 (4 x 3 ) rijen op (Elke rij uit de tabel kinderen wordt gecombineerd met elke rij uit de tabel leden.).

Als er vijftig gezinnen lid zijn van de buurtvereniging en die gezinnen zouden samen honderd kinderen hebben, dan levert deze query 50 x 100 = 5000 rijen op in de combinatietabel waarvan de meeste rijen geen relevante informatie opleveren.


De organisatie wil alleen de combinatie van de roepnaam bij de juiste achternaam en adres. Daarvoor zoeken we in de beide tabellen naar iets gemeenschappelijks. In dit voorbeeld is dat de kolom lidnr. Wanneer er voor een rij geldt dat het lidnummer in de tabel leden gelijk is aan het lidnr in de tabel kinderen dan zal het een gewenste combinatie opleveren. In de SQL-opdracht komt deze voorwaarde achter de WHERE‑component te staan:

SELECT roepnaam, naam, adres
FROM leden, kinderen
WHERE leden.lidnr = kinderen.lidnr
 



In de WHERE-component komt twee keer de kolom lidnr voor. Om duidelijk te maken welke kolom je bedoelt, zet je voor de kolomnaam de naam van de tabel en een punt. De aanduiding van een kolom heeft de volgende basisstructuur: tabelnaam.kolomnaam .

In dit voorbeeld staat de informatie die je wilt hebben in twee tabellen verspreid. Het kan ook voorkomen dat je drie of meer tabellen moet gebruiken om de informatie te krijgen, die je wilt hebben. In dat geval zet je achter de FROM-component de namen van de tabellen en koppel je achter de WHERE-component steeds twee tabellen aan elkaar.

★ Aan de slag 32

We willen een overzicht van alle rekeninghouders en het saldo van hun rekening hebben. Bekijk de volgende query:

SELECT rekeninghouder, saldo
FROM rekeninghouder, rekening
 

  1. Leg in eigen woorden uit waarom de tabel die deze query oplevert fout is.
  2. Uit hoeveel rijen bestaat de resultaattabel van deze query?
  3. Herschrijf deze query zodat de juiste lijst wordt geleverd.

★ Aan de slag 33

Dit onderdeel gaat over het combineren van tabellen in een query. Dezelfde kolomnaam kan meerdere keren voorkomen. Bekijk het volgende voorbeeld:

SELECT bank_id, bank, rekening_id
FROM bank, rekening
WHERE bank_id = bank_id

Pas deze query zo aan, zodat er geen onduidelijkheid meer bestaat over de kolomnamen.

Test de herschreven query in het programma Adminer.

Tabellen combineren - II

De ING wil een overzicht van alle geblokkeerde rekeningen waar nog wel geld op is gestort. Daarvoor is informatie nodig uit twee tabellen. In de query moeten meerdere voorwaarden worden opgenomen:
 

  1. Het rekening_id uit de tabel rekening moet gelijk zijn aan rekening_id bij uit de tabel transactie.
  2. De status van de rekening moet geblokkeerd zijn.
  3. Het type van de transactie moet ST zijn.
     

De uit te voeren query luidt dan:

SELECT rekening_id, bedrag
FROM rekening, transactie
WHERE rekening_bij = rekening_id
      AND type     = 'ST'
      AND status   = 'geblokkeerd'
 

★ Aan de slag 34

Bekijk de volgende query.

SELECT rekeninghouder
FROM   rekeninghouder, rekening
WHERE  rekeninghouder.rekeninghdr_id = rekening.rekeninghdr_id
       AND saldo >= 1500

Leg uit wat deze query selecteert.

 

★ Aan de slag 35

 

  1. Stel de query op die de naam van de persoon geeft die een saldo heeft van precies 3831,34 euro.
  2. Stel de query op die bij elk rekening ook het bijbehorende pasnummer vermeldt.

 

★ Aan de slag 36

 

  1. Maak een lijst met de NAW-gegevens van rekeninghouders en de namen van de banken waar de rekeninghouders hun een betaalrekening hebben.
  2. Maak een lijst van de rekeninghouders waarvan de rekening is geblokkeerd of is opgeheven.

Tabellen combineren - III

Tot nu toe hebben we in de SQL-tool telkens queries uitgevoerd op de database transacties. We gaan de tool echter ook inzetten bij de andere database: de cd-dvd database.

De cd-dvd database bestaat uit 4 tabellen. In de tabel leners staan de gegevens van mensen die cd's of dvd's hebben geleend. Denk daarbij aan de voornaam, achternaam, adres, postcode, woonplaats en telefoonnummer. Elke lener heeft een uniek nummer, het zogenaamde admnr (administratienummer). In de tabel uitlening wordt bijgehouden wie wat heeft geleend en wanneer iets is teruggebracht. Een omschrijving van alle cd's en/of dvd's is terug te vinden in de tabel cd_dvd. In deze tabel staan de namen van artiesten, de titels van de cd/dvd, het jaar waarin de cd/dvd is uitgebracht, het genre waartoe het nummer behoort. Iedere cd en/of dvd wordt in deze tabel geïdentificeerd aan de hand van het cdid.

Voor de weergave van de tabellenstructuur van de cd-dvd database gebruiken we, net als bij de database transacties, een strokendiagram:
 

 

 

★ Aan de slag 37

Maar voor de onderstaande opgaven gebruik van de CD/DVD database.
 

  1. Noem de artiest/band van de cd waarop het nummer Waterloo staat. In het resultaat mogen géén duplicaten staan.
  2. Noem het adminnr en de voor- en achternaam(en) van de lener(s) die ooit de cd waarop het nummer She loves you staat, heeft (hebben) geleend.

H Functies

Functies

De ING wil graag een overzicht hebben van het totale saldo op alle ING-rekeningen. In SQL bestaan een aantal functies waarmee dit soort vragen beantwoord kunnen worden. De functie SUM (Engelse woord voor optellen) berekent de som van de getallen in een kolom:

SELECT SUM(saldo)
FROM bank, rekening
WHERE bank.bank_id = rekening.bank_id
AND bank = 'ING'

Het resultaat van de query is één getal; namelijk de som van alle waarden in de kolom saldo:
 



Boven het totale bedrag staat SUM(saldo). We kunnen een kolom ook een andere naam geven. Dat doe je met behulp van het keyword AS.

SELECT SUM(saldo) AS 'totale saldo van alle ING-rekeningen'
FROM bank, rekening
WHERE bank.bank_id = rekening.bank_id
AND bank = 'ING'


SQL kent de volgende functies:

Functie Betekenis
AVG Berekent het gemiddelde van de getallen in een kolom.
COUNT Telt het aantal waarden in een kolom of telt het aantal rijen in een tabel.
MAX Bepaalt de grootste waarde in een kolom.
MIN Bepaalt de kleinste waarde in een kolom.
SUM Berekent de som van de getallen in een kolom.


Wanneer we willen weten hoeveel rekeninghouders er in heel Nederland wonen behalve die in Rotterdam dan kunnen we daarvoor de volgende query opstellen:

SELECT COUNT(*) AS 'Aantal rekeninghouders buiten Rotterdam'
FROM rekeninghouder
WHERE plaats != 'Rotterdam'

 

 

★ Aan de slag 38

Je hebt gelezen dat de functie SUM de som van de getallen in een kolom berekent.
 

  1. Wat is het resultaat van de volgende query?

    SELECT SUM(bedrag)
    FROM transactie
     
  2. Wat is er mis met de volgende query?

    SELECT SUM(type)
    FROM transactie

     

★ Aan de slag 39

Je hebt gelezen dat de functie SUM de som van de getallen in een kolom berekent.
 

  1. Leg uit wat de volgende query selecteert:

    SELECT COUNT(*)
    FROM rekeninghouder
    WHERE NOT plaats = 'Heerlen'
     
  2. Het resultaat van deze query is één getal. Hoe kun je ervoor zorgen dat boven het getal de tekst 'rekeninghouders' komt te staan?

 

★ Aan de slag 40

Voor deze opdracht maken we gebruik van de SQL-tool database transacties.

 

  1. Hoeveel betaalpassen zijn er in totaal uitgegeven?
  2. Wat is het hoogste saldo dat er op een rekening staat?
  3. Wat is het laagste saldo dat er op een rekening staat?

 

 

★ Aan de slag 41

Voor deze opdracht maken we gebruik van de SQL-tool database transacties.

 

  1. Wat is de datum van de oudste transactie die heeft plaatsgevonden?
  2. Wat is de datum van de meest recente transactie?
  3. Wat is het gemiddelde bedrag van alle transacties?

 

★ Aan de slag 42

Voor deze opdracht maken we gebruik van de database cd-dvd.

 

  1. Hoeveel cd's van Genesis zijn in de database opgenomen?
  2. Uit welk jaar dateert de oudste cd die in de database terug te vinden is?
  3. In hoeveel verschillende woonplaatsen wonen de leners?

 

 

 

★ Aan de slag 44

Voor deze opdracht maken we gebruik van de SQL-tool cd-dvd database.

 

  1. Hoeveel leners hebben ooit een cd of dvd geleend? Geef de resultaattabel de naam 'ooit geleend'.
  2. Kunnen in deze resultaattabel dezelfde leners meerdere keren voorkomen? Licht je antwoord toe.

I Groeperen

Inleiding

In het vorige onderdeel hebben we bekeken hoeveel rekeninghouders er niet in Rotterdam wonen. Daarna hebben we ook nog kunnen bekijken hoeveel rekeninghouders wel in Rotterdam wonen. Het wordt lastiger als we een overzicht van het aantal rekeninghouders per plaats willen opvragen. We zouden dan kunnen beginnen om eerst een lijst van woonplaatsen te maken:

SELECT DISTINCT plaats
FROM rekeninghouder

Om vervolgens per woonplaats het aantal rekeninghouders op te vragen:

Bijvoorbeeld:

SELECT COUNT(*) AS 'Maastricht'
FROM rekeninghouder
WHERE plaats = 'Maastricht'

 
Het nadeel van deze methode is dat je de 2de query maar liefst 22 keer moet herhalen om van alle plaatsen het aantal rekeninghouders te weten te komen!

 

 

De bovenstaande methode is erg omslachtig. Gelukkig kent SQL een statement om rijen te groeperen: GROUP BY.
In dit voorbeeld zou de tabel rekeninghouder gegroepeerd moeten worden op basis van woonplaats. Daarna kunnen we dan opvragen hoeveel rekeninghouders er in elke woonplaats wonen.

SELECT plaats, COUNT(*)
FROM rekeninghouder
GROUP BY plaats

Het resultaat van deze query is een lijst met woonplaatsen met daarachter het aantal rekeninghouders uit de desbetreffende woonplaatsen.

Het is ook mogelijk om te groeperen op twee kolommen. We willen bijvoorbeeld weten of er rekeningen bestaan die dezelfde status en hetzelfde saldo hebben. Daarvoor voeren we de onderstaande query uit:

SELECT saldo, status, COUNT(*)
FROM rekening
GROUP BY saldo, status

Hieronder zie je een stukje van het resultaat:

Voorwaarden stellen I - where

Met behulp van de WHERE-component kunnen we een voorwaarde stellen aan een query. Die voorwaarde wordt opgelegd aan de rijen. Wanneer we een overzicht willen van alle rekeninghouders per woonplaats die wonen op een adres waar 'kade' in voorkomt dan kan dat met de volgende query:

SELECT plaats, COUNT(*)
FROM rekeninghouder
WHERE adres LIKE '%kade%'
GROUP BY plaats

 

 

 

 

Belangrijk om te weten; SQL hanteert een vaste volgorde bij het opbouwen van de queries.
 

Bij het opbouwen van een query moet je de volgende volgorde hanteren:

1 select
2 from
3 where
4 group by
5 having
6 order by

 

 

 

 

 

★ Aan de slag 46

Bekijk de volgende query:

SELECT status, COUNT(*)
FROM rekening
GROUP BY status

Leg in eigen woorden uit wat deze query selecteert en hoe de lijst gepresenteerd wordt.  Voer de query daarna een keer uit en controleer of je gedachtegang klopte.

★ Aan de slag 47

In de database van de tennisvereniging wordt in de tabel leden van elk lid van de tennisvereniging vermeld of deze man of vrouw is.


Bekijk nu de volgende queries:

SELECT COUNT(*)
FROM leden
WHERE geslacht = 'V'

SELECT COUNT(*)
FROM leden
WHERE geslacht = 'M'


Herschrijf deze queries tot één query door de tabel leden te groeperen op basis van het geslacht.

★ Aan de slag 48

 

Bekijk de volgende query die betrekking heeft op de database van de tennisvereniging:

SELECT woonplaats, geslacht, COUNT(*)
FROM leden
GROUP BY woonplaats, geslacht

Leg in eigen woorden uit wat deze query selecteert en hoe de lijst gepresenteerd wordt. Voer de query daarna een keer uit en controleer of je gedachtegang klopte.

★ Aan de slag 49

We vestigen onze aandacht weer op de database transacties.

 

Bekijk de volgende query:

SELECT status_ov, COUNT(*)
FROM transactie
WHERE status_ov LIKE '_K'
GROUP BY status_ov

Leg in eigen woorden uit wat deze query doet en hoe de lijst gepresenteerd wordt. Voer de query daarna een keer uit en controleer of je gedachtegang klopte.

Voorwaarden stellen II - having

Het kan nodig zijn om een voorwaarde te stellen aan de groepen op basis van bepaalde groepseigenschappen.

 

Stel bijvoorbeeld dat je een overzicht wil hebben van alle woonplaatsen waar meer dan drie rekeninghouders wonen. Deze query kan je als volgt opbouwen: met het group-by-statement kunnen we opvragen in welke plaatsen de rekeninghouders wonen en met het having-statement kunnen we vervolgens bepalen dat we alleen die woonplaatsen willen zien waarin meer dan drie rekeninghouders wonen.

De query wordt als volgt:

SELECT plaats, COUNT(*)
FROM rekeninghouder
GROUP BY plaats
HAVING COUNT(*) > 3
 


Belangrijk om te weten:
Het having-statement heeft alleen betrekking op het group-by-statement!


Het WHERE-statement gebruik je wanneer je een voorwaarde oplegt aan een of meerdere rijen.

Het HAVING-statement gebruik je wanneer je een voorwaarde oplegt aan een of meerdere groepen.

 

Samengevat

where rijen
having groepen

 

★ Aan de slag 50

Voor deze opdracht maken we gebruik van de database transacties.

Je kunt bij het groeperen van rijen ook de HAVING-statement gebruiken.

  1. Leg uit wat de HAVING-statement doet.
  2. Wat is het verschil met de WHERE-statement.
  3. Wat selecteert de volgende query?

    SELECT rekening_bij, SUM(bedrag)
    FROM transactie
    GROUP BY rekening_bij
    HAVING SUM(bedrag) > 1400
  4. Geef van de rekeningen het gemiddelde bedrag dat bijgeschreven is. Alleen die bedragen mogen worden getoond waarbij het gemiddelde bedrag hoger is dan € 1000.

 

 

★ Aan de slag 51

Bekijk de volgende queries:


SELECT COUNT(rekeninghdr_id)
FROM rekeninghouder
WHERE plaats = 'Haarlem'


SELECT COUNT(rekeninghdr_id)
FROM rekeninghouder
WHERE plaats = 'Maastricht'


Herschrijf deze queries tot één query.

★ Aan de slag 52

Voor deze opdracht maken we gebruik van de database transacties.

  1. Geef per woonplaats het adres van de rekeninghouders.
  2. Aan welke rekeningen zijn er meer dan twee betaalpassen gekoppeld?

★ Aan de slag 53

Voor deze opdracht maken we gebruik van de database transacties.
 

Vraag
In welke woonplaats wonen er meer dan 3 rekeninghouders?

 

★ Aan de slag 54

Voor deze opdracht maken we gebruik van de cd-dvd database.
 

Vraag
Stel de query die per lener het aantal uitleningen weergeeft. Alleen die leners mogen worden getoond die meer dan 3 keer iets hebben geleend.

★ Aan de slag 55

Voor deze opdracht maken we gebruik van de cd-dvd database.
 

  1.  Maak een lijst van het aantal nummers per cd.
     
  2.  Welke cd's hebben minder dan 10 nummers op de cd staan?

★ Aan de slag 56

Voor deze opdracht maken we gebruik van de cd-dvd database.

SQL kent ook de BETWEEN-operator. Met deze operator kan je waarden selecteren die tussen twee grenzen in liggen. De grenswaarden tellen hierbij mee als het om getallen (integers) gaat.

 

Bijvoorbeeld
bedrag BETWEEN 25 and 50.

 

Vraag
Maak een lijst van alle cd's die tussen de 4 en 8 nummers bevatten.

 

 

J Subqueries

Subqueries I

De ING wil graag weten van welke type (acceptgiro, betaalautomaat, geldautomaat, etc.) de oudste transactie was. Deze vraag is niet zo makkelijk te beantwoorden. Dit komt omdat je eigenlijk twee opdrachten moet uitvoeren om de vraag te beantwoorden:
 

  1. Zoek de datum van de oudste transactie.
  2. Zoek uit wat voor type bij deze transactie hoort.


Het antwoord op het eerste deel van de vraag kun je vinden met de volgende query:

SELECT MIN(datum)
FROM transactie


 

Het resultaat van deze query is '2015-12-19'. Wanneer je dit antwoord hebt, is het tweede deel van de vraag niet zo moeilijk meer te beantwoorden:

SELECT type
FROM transactie
WHERE datum = '2015-12-19'
 



Deze twee queries kun je combineren tot een enkele query:

SELECT type
FROM transactie
WHERE datum = (SELECT MIN(datum)
               FROM transactie)
 

Bij het where-statement is, in plaats van het antwoord van de eerste query, de hele eerste query ingevuld! Dit wordt een subquery genoemd.

Er is een belangrijk verschil tussen een query en een subquery: het select-statement van de subquery mag maar één kolom bevatten. Bij een voorwaarden met een subquery wordt het resultaat namelijk altijd vergeleken met één waarde uit één kolom, bijvoorbeeld datum = '2015-12-19'.

★ Aan de slag 57

Je hebt gelezen wat een subquery is.

  1. Leg in eigen woorden uit wat een subquery is.
  2. Noem een belangrijk verschil tussen een query en een subquery.
  3. Geef een voorbeeld van een subquery.
  4. Beschrijf wat je met deze query uit onderdeel c selecteert.

 

Subqueries II

In het vorige voorbeeld waarbij de oudste transactiedatum werd gevraagd, komen de gegevens uit één tabel. Dit is natuurlijk niet altijd het geval.
Wanneer we willen weten welke rekeninghouders het hoogste saldo op hun rekening hebben staan, komen de gegevens uit twee tabellen: rekeninghouder en rekening.

De vraag die beantwoord moet worden, bestaat uit drie opdrachten:

  1. Zoek het hoogste saldo op wat in de tabel rekening voorkomt.
  2. Zoek op wat de id's zijn van de rekeninghouders die dit bedrag op hun rekening hebben staan (kunnen er meerdere zijn).
  3. Zoek de namen op die horen bij de id's van de rekeninghouders.

 

Het antwoord op de eerste vraag wordt gevonden met de query:

SELECT MAX(saldo)
FROM rekening

Omdat de tabel rekening geen namen van de rekeninghouders bevat, moeten we eerst de id's van de rekeninghouders zoeken die het hoogste saldo op zijn/haar rekening hebben staan:

SELECT rekeninghdr_id
FROM rekening
WHERE saldo = (SELECT MAX(saldo)
               FROM rekening)


Tenslotte moeten de juiste namen gezocht worden bij de rekeninghdr_id's:

SELECT rekeninghouder
FROM rekeninghouder
WHERE rekeninghdr_id IN
           (SELECT rekeninghdr_id
            FROM rekening
            WHERE saldo = (SELECT MAX(saldo)
                           FROM rekening))


Je ziet dat in plaats van de operator '=' de operator IN is gebruikt. Het is namelijk mogelijk dat er meer rekeninghouders zijn die het hoogste saldo op hun rekening hebben staan.

 

(Bovenstaande vraag had ook kunnen worden beantwoord door het samenvoegen van de tabellen rekeninghouder en rekening en dat gecombineerd met één subquery.)

 

★ Aan de slag 58

Bekijk de volgende query:

SELECT bank
FROM bank
WHERE bank_id IN
     (SELECT bank_id
      FROM rekening
      WHERE saldo = (SELECT MIN(saldo)
                     FROM rekening))

  1. Het is lastig om deze query in één keer te doorgronden. Ontrafel deze queries in subqueries en beschrijf van iedere query afzonderlijk wat er gebeurt.
  2. Waarom wordt in de derde regel de IN-operator gebruikt en niet de operator '='?
  3. Beschrijf wat je met deze query selecteert.

 

★ Aan de slag 59

Voor deze opdracht maken we gebruik van de database transacties.
 

  1. Geef de rekeninghouder-id van de transactie van het hoogste geldbedrag dat is bijgeschreven op een rekening.
  2. Geef de namen van de rekeninghouders die bij de bank meerdere betaalpassen voor hun rekening(-en) hebben ontvangen.

★ Aan de slag 60

 

Hieronder staan twee queries:

Query 1

SELECT rekeninghouder
FROM rekeninghouder
WHERE rekeninghdr_id IN (SELECT rekeninghdr_id
                         FROM rekening
                         WHERE saldo = (SELECT MAX(saldo)
                                        FROM rekening))

 

Query 2

SELECT rekeninghouder
FROM rekeninghouder, rekening
WHERE rekeninghouder.rekeninghdr_id = rekening.rekeninghdr_id
AND saldo = (SELECT MAX(saldo)
             FROM rekening)

 

  1. Wat is het resultaat van de eerste query?
  2. Wat is het resultaat van de tweede query?
  3. In de eerste query staat rekeninghdr_id IN en in de tweede query rekeninghouder.rekeninghdr_id = rekening.rekeninghdr_id. Leg uit waarom.

 

 

★ Aan de slag 61

Voor deze opdracht maken we gebruik van de cd-dvd database.
 

  1. Geef de voor- en achternaam van de lener die meer dan 3 keer iets heeft geleend.
     
  2. Geef het adminnr en de voor- en achternaam van leners die iets geleend hebben, maar dat nog niet hebben teruggebracht.

 

K Gegevens bewerken

Inleiding

Tot nu toe heb je queries geschreven waarmee je gegevens uit een database kunt opvragen. In SQL bestaan er ook commando's waarmee je in een database gegevens kunt invoeren, bewerken of verwijderen.

Toevoegen

Voor het toevoegen van een rij aan een tabel maken we gebruik van de INSERT INTO-component:

Achter INSERT INTO wordt de naam van de tabel vermeld. Bij VALUES geef je aan welke waarden in de rij van de tabel moeten worden opgenomen. Let erop dat alfanumerieke waarden tussen aanhalingstekens moeten worden ingevoerd. Bij numerieke gegevens, dit zijn gegevens waarmee je kunt rekenen, gebeurt dit niet. Een datum wordt standaard tussen aanhalingstekens ingevoerd.

De bank wil in de tabel rekeninghouder een rekeninghouder toevoegen:

INSERT INTO rekeninghouder
VALUES (54,'Ralph Bosman', 'Chopinstraat 4', 'Amsterdam', '020-7787262');

De tabel rekeninghouder bestaat uit 5 kolommen:rekeninghdr_id, rekeninghouder, adres, plaats en telefoonnr. De kolom rekeninghdr_id bevat alle sleutels. Alle andere velden zijn van het type tekst.

Na het uitvoeren van de query is er een rekeninghouder toegevoegd. Aan de rechterkant van het scherm is te zien dat er nu 54 rekeninghouders staan in de tabel rekeninghouder. Dat waren er 53.

 

★ Aan de slag 64

Voor deze opdracht maken we gebruik van de SQL-tool database transacties.                                                               

Transacties

Neem de onderstaande query over in de tool en voer de query uit:

INSERT INTO rekeninghouder
VALUES (54,'Ralph Bosman', 'Chopinstraat 4', 'Amsterdam', '020-7787262');

Controleer of het aantal rekeninghouders inderdaad met 1 is toegenomen
.

 

Wijzigen

Bij het invoeren van de gegevens van de nieuwe rekeninghouder heeft de bank een fout gemaakt. Ralph Bosman woont niet op nummer 4, maar op nummer 44. Alle andere gegevens blijven hetzelfde. Dit betekent dat in de tabel rekeninghouder het adres zal moeten worden aangepast. In SQL kan daarvoor de opdracht UPDATE gegeven worden:

UPDATE rekeninghouder
SET adres = 'Chopinstraat 44'
WHERE rekeninghdr_id = 54;


 

★ Aan de slag 65

Voor deze opdracht maken we gebruik van de SQL-tool database transacties.

Transacties

In een eerdere opdracht hebben we geconstateerd dat van een rekeninghouder die van Goes naar Alkmaar was verhuisd, nog het oude telefoonnummer werd vermeld. We gaan dat nummer aanpassen. Het gaat om de rekeninghouder Joost Oosterhout waarvan de volgende gegevens bekend zijn:

rekeninghdr_id: 29
rekeninghouder: Joost Oosterhout
straat: Vermeerstraat 6
plaats Alkmaar
telefoonnummer: 0113-575758

Het nieuwe telefoonnummer van Joost Oosterhout wordt 072-9394516.

  1. Schrijf een query waarmee de gegevens van Joost worden aangepast en het nieuwe telefoonnummer in de tabel rekeninghouder komt te staan. Voer de query uit en kijk naar het resultaat.
  2. Voer nu de onderstaande query uit en controleer of het telefoonnummer inderdaad gewijzigd is:

    SELECT *
    FROM rekeninghouder
    WHERE rekeninghouder = 'Joost Oosterhout';

Verwijderen

We kunnen de zojuist toegevoegde rij ook weer verwijderen.
Voor het verwijderen van een rij wordt in SQL DELETE FROM gebruikt:

DELETE FROM rekeninghouder
WHERE rekeninghdr_id = 54;


Het is ook mogelijk om alle rijen uit de tabel rekeninghouder in één keer te verwijderen.
Daarvoor hoeft slechts de WHERE-component in de query weggelaten te worden.

De DELETE FROM-component verwijdert alleen de inhoud van een tabel.
Met DROP TABLE wordt behalve de complete inhoud ook de tabel zelf verwijderd:

DROP TABLE rekeninghouder;


 

In onze SQL-tool kunnen we de oorspronkelijke database herstellen door te klikken op de knop 'Database herstellen'.




Na het bevestigen van de opdracht bestaat de database transacties wederom uit 5 tabellen inclusief de tabel rekeninghouder.


 

★ Aan de slag 66

Voor deze opdracht maken we gebruik van de SQL-tool database transacties.

Transacties
  1. Verwijder de tabel bank uit de database transacties.
  2. Klik op Database herstellen om de oorspronkelijk te herstellen en kijk of de tabel bank weer zichtbaar is.

Toets 2

Adaptieve toets

In de volgende toets wordt getoetst wat je inmiddels zou moeten weten over het onderwerp databases.
Maak de toets.

Databases

  • Het arrangement Hoofdstuk Databases is gemaakt met Wikiwijs van Kennisnet. Wikiwijs is hét onderwijsplatform waar je leermiddelen zoekt, maakt en deelt.

    Auteur
    Wikiwijs Wikiwijs
    Laatst gewijzigd
    09-07-2019 08:06:23
    Licentie

    Dit lesmateriaal is gepubliceerd onder de Creative Commons Naamsvermelding 3.0 Nederlands licentie. Dit houdt in dat je onder de voorwaarde van naamsvermelding vrij bent om:

    • het werk te delen - te kopiëren, te verspreiden en door te geven via elk medium of bestandsformaat
    • het werk te bewerken - te remixen, te veranderen en afgeleide werken te maken
    • voor alle doeleinden, inclusief commerciële doeleinden.

    Meer informatie over de CC Naamsvermelding 3.0 Nederland licentie.

    Aanvullende informatie over dit lesmateriaal

    Van dit lesmateriaal is de volgende aanvullende informatie beschikbaar:

    Leerniveau
    HAVO 4; VWO 6; HAVO 5; VWO 4; VWO 5;
    Leerinhoud en doelen
    Informatica;
    Eindgebruiker
    leerling/student
    Moeilijkheidsgraad
    gemiddeld
    Trefwoorden
    arrangeerbare, leerlijn

    Gebruikte Wikiwijs Arrangementen

    VO-content Informatica. (z.d.).

    Databases

    https://maken.wikiwijs.nl/100748/Databases

  • Downloaden

    Het volledige arrangement is in de onderstaande formaten te downloaden.

    Metadata

    LTI

    Leeromgevingen die gebruik maken van LTI kunnen Wikiwijs arrangementen en toetsen afspelen en resultaten terugkoppelen. Hiervoor moet de leeromgeving wel bij Wikiwijs aangemeld zijn. Wil je gebruik maken van de LTI koppeling? Meld je aan via info@wikiwijs.nl met het verzoek om een LTI koppeling aan te gaan.

    Maak je al gebruik van LTI? Gebruik dan de onderstaande Launch URL’s.

    Arrangement

    IMSCC package

    Wil je de Launch URL’s niet los kopiëren, maar in één keer downloaden? Download dan de IMSCC package.

    Voor developers

    Wikiwijs lesmateriaal kan worden gebruikt in een externe leeromgeving. Er kunnen koppelingen worden gemaakt en het lesmateriaal kan op verschillende manieren worden geëxporteerd. Meer informatie hierover kun je vinden op onze Developers Wiki.

  • Wikiwijs is een dienst van