Presentatie H9 - informatica.haperen.com

Hoofdstuk 9
Inleiding
SQL: basis
functies
Relationele databases
en
SQL
subqueries
twee tabellen
exists
oefeningen
Sondervick College – Veldhoven






Database
Gegevensbank = verzameling van gegegevens
DBMS
Database Management systeem
Relationeel model
Een manier om een database te organiseren, nl. met
tabellen
Een rij in de tabel:
Alle gegevens van een bepaalde kaart of record
Kolommen in de tabel:
Worden ook wel velden genoemd
Een aantal DBMS’en:
Access
Filemaker
Oracle
MySQL
Sondervick College - Veldhoven
Taken van een databasesysteem
Het
opslaan van gegevens en het
mogelijk maken van wijzigingen
Het
kunnen opvragen van informatie
en het verwerken van informatie
Het
bewaken van de integriteit
(dus het voorkomen van fouten)
Sondervick College - Veldhoven
De voorbeelden op de volgende dia’s hebben betrekking op
de database Bibliotheek, met de volgende tabellen:
Tabel: LEERLINGEN
LLNR - VOORNAAM - TUSSENVOEGSEL - ACHTERNAAM STRAAT - HUISNUMMER - POSTCODE - PLAATS TELEFOON - GESLACHT - GEB_DATUM - KLAS
Tabel: AUTEURS
AUTEURNR - VOORNAAM - VOORLETTERS - TUSSENVOEGSEL - ACHTERNAAM - GEB_DATUM - STERF_JAAR
Tabel: BOEKEN
BOEKNR - AUTNR - TITEL - RUBRIEK
Tabel: EXEMPLAREN
EXNR - BOEKNR - UITLEENBAAR - STATUS
Sondervick College - Veldhoven
Er komen kolommen voor die alleen maar een
nummer bevatten:
LLNR / AUTEURNR / BOEKNR / EXNR
Reden: • Een nummer is altijd uniek.
Je kunt twee leerlingen met exact dezelfde
naam hebben, maar de leerlingennummers
verschillen altijd.
• Met een nummer kun je gemakkelijk
verwijzen naar een andere tabel.
Sondervick College - Veldhoven
Basisstructuur van een
eenvoudige query
Sondervick College - Veldhoven
SELECT: Achter SELECT geef je de kolommen
op die je in de uitvoer wilt zien.
Zet je achter select een sterretje in
plaats van kolomnamen, dan krijg je
de inhoud van alle kolommen op het
scherm.
FROM:
Achter FROM geef je de naam van de tabel
(of tabellen) waaruit deze kolommen komen.
Sondervick College - Veldhoven
SELECT
VOORNAAM, TUSSENVOEGSEL,
ACHTERNAAM, KLAS
FROM
LEERLINGEN
ORDER BY
ACHTERNAAM
Door de opdracht ORDER BY
wordt de lijst gesorteerd.
Je kunt ook op twee of meer kolommen laten sorteren,
door achter ORDER BY de twee kolommen op te geven.
Sondervick College - Veldhoven
SELECT
FROM
VOORNAAM, TUSSENVOEGSEL,
ACHTERNAAM, KLAS
LEERLINGEN
ORDER BY 3 DESC
Nu wordt op de 3e kolom
gesorteerd (ACHTERNAAM)
DESC betekent: Aflopend (Descending), dus van Z naar A.
Als er niets vermeld wordt (of ASC = ascending)
wordt oplopend gesorteerd.
Sondervick College - Veldhoven
SELECT
PLAATS
FROM
LEERLINGEN
Het resultaat is een lange rij van plaatsnamen.
Die plaatsnamen kunnen meerdere keren voorkomen.
SELECT
FROM
DISTINCT PLAATS
LEERLINGEN
Door DISTINCT wordt elke
plaats maar één keer afgedrukt.
Sondervick College - Veldhoven
Er kunnen voorwaarden worden gesteld aan de rijen, die
moeten worden afgedrukt.
Voorwaarden geef je achter het sleutelwoord: WHERE
SELECT
FROM
WHERE
VOORNAAM, TUSSENVOEGSEL,
ACHTERNAAM
LEERLINGEN
GESLACHT = "M"
Alleen de leerlingen, die als
geslacht M hebben, worden
afgedrukt
Sondervick College - Veldhoven
Je mag wildcards gebruiken bij de voorwaarden, maar je
moet dan geen = maar like als vergelijkingsoperator
gebruiken.
SELECT
FROM
WHERE
VOORNAAM, TUSSENVOEGSEL,
ACHTERNAAM
LEERLINGEN
VOORNAAM like "M*"
Alle leerlingen, waarvan de voornaam
met een M begint, worden afgedrukt
Sondervick College - Veldhoven
*
In Access: er mag alles staan (ook meerdere tekens)
%
Standaard SQL: er mag alles staan (ook meer tekens)
?
In Access: één willekeurig teken
_
Standaard SQL: één willekeurig teken
< en >
Kleiner dan / groter dan
<= en >= Kleiner dan of gelijk aan / groter dan of gelijk aan
<>
Niet gelijk aan
Sondervick College - Veldhoven
SELECT
FROM
*
LEERLINGEN
WHERE TELEFOON
IS NULL
Dit betekent:
Er is niets ingevuld
Sondervick College - Veldhoven
Als er aan meer dan één voorwaarde moet worden
voldaan gebruik je AND
SELECT *
FROM
LEERLINGEN
WHERE ACHTERNAAM = "Bakker"
AND TUSSENVOEGSEL = "de"
AND VOORNAAM = "Jantine"
Alleen de gegevens van Jantine de
Bakker worden afgedrukt
Sondervick College - Veldhoven
Als aan de ene of aan de andere voorwaarde moet
worden voldaan gebruik je OR
Deze OR is niet exclusief. D.w.z.: rijen die aan allebei de
voorwaarden voldoen worden ook afgedrukt.
SELECT *
FROM
LEERLINGEN
WHERE
KLAS = "4A" OR KLAS = "4B"
Leerlingen die in klas 4A of 4B zitten worden afgedrukt, dus je
krijgt een lijstje van de leerlingen van 4a en van 4b
Sondervick College - Veldhoven
Het aantal getallen of
woorden in de kolom
COUNT(KOLOMNAAM)
SUM(KOLOMNAAM)
De som van alle getallen in
de kolom
MAX(KOLOMNAAM)
MIN(KOLOMNAAM)
De grootste
waarde in de kolom
AVG(KOLOMNAAM)
De kleinste
waarde in de kolom
De gemiddelde
waarde in de kolom
Sondervick College - Veldhoven
We willen weten welke leerling de hoogste boete heeft
betaald na het lenen van een boek
Dat doen we eerst m.b.v. twee queries, of als je de naam
van de leerling ook wilt weten, zelfs drie
1: Wat is de maximale boete die betaald is?
2: Welke leerling(en) moesten die boete betalen?
Daarna laten we zien hoe het ook met één query
kan m.b.v. subqueries
Sondervick College - Veldhoven
We gaan eerst de maximale boete uitzoeken m.b.v. een query:
SELECT MAX (BOETE) FROM UITLENINGEN
Die maximale boete blijkt 4 euro te zijn
Dan zoeken we uit welk leerlingennummer uit de
Deze
Deze
twee
twee queries
queries
tabel UITLENINGEN bij die maximale boete
hoort.
gaan
gaan we
we
SELECT LLNR
combineren
combineren tot
tot
één
een query
query
FROM
UITLENINGEN
WHERE BOETE = 4
Sondervick College - Veldhoven
SELECT LLNR FROM UITLENINGEN
Dit is een
subquery
WHERE BOETE =
( SELECT MAX (BOETE)
FROM UITLENINGEN
)
Nu weet je alleen het LLNR en
nog niet de naam. Als je die wilt weten
moet er nog een subquery bij.
Die staat op de volgende dia
Sondervick College - Veldhoven
SELECT
FROM
WHERE
NAAM, KLAS
LEERLINGEN
LLNR IN
(
SELECT
FROM
WHERE
(
LLNR
UITLENING
BOETE =
SELECT MAX(BOETE)
FROM UITLENINGEN
)
)
Sondervick College - Veldhoven
We willen weten wie de auteur is van het boek met de titel:
Dezetabellen
nummers
De woeste wandeling. Daarvoor moeten we twee
moeten gelijk zijn
combineren: AUTEURS en BOEKEN
Tabel Boeken:
Boeknr Auteurnr
Titel
Pub_jaar
Rubriek
88
47
De woeste wandeling
1981
nederl
89
48
Jan van Gent
1968
nederl
Tabel Auteurs:
Auteurnr Voornaam
Achternaam
Geb_jaar
47
Willem Frederik
Hermans
1921
48
Ward
Hermans
Sondervick College - Veldhoven
De query, die de naam van de auteur van het boek met de
titel De woeste wandeling levert, is:
Dit noem je een
SELECT TITEL, VOORNAAM, ACHTERNAAM join
FROM BOEKEN, AUTEURS
WHERE TITEL = "De woeste wandeling" AND
BOEKEN.AUTEURNR = AUTEURS. AUTEURNR
Denk bij een query met twee
tabellen
altijd om de join (zie hierboven)
Sondervick College - Veldhoven
Met GROUP BY kun je groeperen: de rijen van een kolom
worden ingedeeld in groepen, die dezelfde gegevens in een
kolom hebben. Daar kun je dan een functie op toepassen,
bijv. het aantal rijen tellen, of de getallen optellen.
Voorbeeld: geef de plaatsen, waar leerlingen wonen, met
het aantal leerlingen per plaats
SELECT
PLAATS, COUNT(*)
FROM
LEERLINGEN
GROUP BY PLAATS
Het aantal per
plaats wordt geteld
Sondervick College - Veldhoven
Stel je bij het gebruik van GROUP BY een extra
voorwaarde aan de uitkomst van de functie, dan doe je
dat met HAVING
SELECT
Alleen de plaatsen
waar meer dan vijf
leerlingen wonen worden
afgedrukt
PLAATS, COUNT(*)
FROM
LEERLINGEN
GROUP BY PLAATS
HAVING
COUNT(*) > 5
Sondervick College - Veldhoven
Geef de leerlingen die nooit een boek geleend hebben
De query, waarin je om de boeken vraagt die de leerling
heeft geleend, moet geen resultaten opleveren!
SELECT
VOORNAAM, ACHTERNAAM
FROM
LEERLINGEN
Die zijn er dus niet!
WHERE NOT EXISTS
( SELECT * FROM UITLENINGEN
WHERE UITLENINGEN.LLNR = LEERLINGEN.LLNR)
Sondervick College - Veldhoven
SQL-oefeningen
Er komen nu een aantal vragen die
betrekking hebben op een database
waarin de
administratie van
rundveehouderijen
wordt bijgehouden
Bedenk na elke opgave zelf wat de
sql-query zal zijn, druk dan op
PageDown of klik op de muis
Sondervick College - Veldhoven
Rundvee administratie:
De tabellen en velden
Kolommen
of Velden
Bedrijven
bedrijfsnr naam adres postc plaats tel
Koeien
koenr vader moeder gebdat bedrijfsnr
Tabellen
Jaarprod
koenr jaar kgmelk vetgeh eiwitgeh
Sondervick College - Veldhoven
SQL, de basis
Geef naam, adres en plaats van
het bedrijf met nummer 213
Select naam,adres,plaats
From bedrijven
Where bedrijfsnr=‘213’ ;
Sondervick College - Veldhoven
Alle kolommen
Geef alle gegevens van de koeien
van het bedrijf met nummer 213
gesorteerd op geboortedatum
Select *
From koeien
Where bedrijfsnr=‘213’
Order by gebdat ;
Sondervick College - Veldhoven
Duplicaten voorkomen
Geef een lijstje van alle plaatsen
waar bedrijven zijn gevestigd (elke
plaats maar één keer tonen)
Select distinct plaats
From bedrijven ;
Sondervick College - Veldhoven
Samengestelde voorwaarde
Geef de nummers van de koeien
die in 2002 een melkproductie
hadden van meer dan 8000 kg.
Select koenr
From jaarprod
Where jaar=‘2002’
and kgmelk>8000;
Sondervick College - Veldhoven
Wildcards
Geef naam, adres en plaats van
alle bedrijven waarvan de postcode
met 92 begint
Select naam, adres, plaats
From bedrijven
Where postcode like ‘92*’ ;
Sondervick College - Veldhoven
De rijen tellen
Geef het aantal koeien dat ze op
het bedrijf met nummer 213 hebben
Select count(*)
From koeien
Where bedrijfsnr=‘213’ ;
Sondervick College - Veldhoven
De som van alle getallen in een kolom
Geef het totaal aantal kg. melk
dat in 2002 is gemolken
Select sum(kgmelk)
From jaarprod
Where jaar=‘2002’ ;
Sondervick College - Veldhoven
Verdeel in groepjes en tel de aantallen daarin
Geef een lijstje van alle
bedrijfsnummers met het aantal
koeien op dat bedrijf
Select bedrijfsnr, count(*)
From koeien
Group by bedrijfsnr;
Sondervick College - Veldhoven
Groeperen
N.B.:achter group by en achter select altijd
dezelfde kolomnamen zetten !
Geef een lijstje van alle koenummers met het aantal kg. melk
dat ze in totaal hebben gegeven
Select koenr, sum(kgmelk)
From jaarprod
Group by koenr;
Sondervick College - Veldhoven
Having
Geef een lijstje van alle moeders
die meer dan 10 kinderen hebben
Select moeder, count(*)
From koeien
Group by moeder
Having count(*)>10;
Sondervick College - Veldhoven
Een subquery
Geef het nummer van de koe
die in 2002 de grootste
hoeveelheid melk gaf
Select koenr
from jaarprod where
Kgmelk=(select max(kgmelk) from
jaarprod where jaar=‘2002’) ;
Sondervick College - Veldhoven
Subquery met in
Geef een lijstje van de koenummers
van de koeien van bedrijven die
gevestigd zijn in Oenkerk
Select koenr from koeien
Where bedrijfsnr in (select
bedrijfsnr from bedrijven where
plaats = ‘Oenkerk’) ;
Sondervick College - Veldhoven
Twee tabellen gebruiken
Geef van koenr 213 de geboortedatum en de naam en plaats van
het bijbehorende bedrijf
Select gebdatum,naam,plaats
From bedrijven, koeien
Where koenr=‘213’ and
bedrijven.bedrijfsnr=koeien.bedrijfsnr;
Sondervick College - Veldhoven
Drie tabellen gebruiken
Geef een lijstje met de nummers en de
geboorte-data van de koeien en de naam en
plaats van het bedrijf van de koeien die in
2002 meer dan 8000 kg. melk gaven.
Select koeien.koenr,gebdat,naam,plaats
From bedrijven, koeien, jaarprod
Where kgmelk>8000 and jaar=‘2002’ and
bedrijven.bedrijfsnr=koeien.bedrijfsnr
and koeien.koenr=jaarprod.koenr;
Sondervick College - Veldhoven
Exists en not exists
Geef alle bedrijfsnrs van bedrijven
die geen koeien hebben die meer
dan 8000 kg. melk hebben gegeven
Select distinct bedrijfsnr from koeien
Where not exists (select koenr
From jaarprod where kgmelk>8000
And jaarprod.koenr=koeien.koenr);
Sondervick College - Veldhoven
Einde