Databases en SQL (les 4 t/m 6): Verder met SQL

• Informatica
• Middelbare school
• havo, vwo
• Leerjaar 4

databases & SQL

Verder met SQL: Structuur van een database en gekoppelde tabellen

Leerdoel

Je begrijpt wat een primaire sleutel en een vreemde sleutel zijn, kent de term redundantie, en weet wat een Entity Relationship Diagram is en hoe je deze kunt toepassen. Daarnaast kun je gegevens uit meerdere tabellen selecteren door ze te koppelen via een gemeenschappelijke kolom.

Unieke waarden

Een database bestaat in de regel uit meerdere tabellen:

• In één databasetabel zullen NOOIT twee exact dezelfde rijen
  
• Hierdoor worden dubbele waarden voorkomen
  
• Dit maakt een database
   efficiënt en betrouwbaar

De primaire sleutel

Door de primaire sleutel is elke rij uniek

Deze sleutel is meestal één kolom (soms meer)

De primaire sleutel mag nooit NULL zijn

Dat heet entiteitsintegriteit

• Er op kunnen vertrouwen dat elke rij ook echt uniek is
  

De refererende of vreemde sleutel

Om tabellen te koppelen moeten primaire sleutels van de ene tabel ook in een andere tabel voorkomen

In die andere tabel heet dit dan een refererende, verwijzende of vreemde sleutel

Redundantie (dubbele data)

• Als een database onnodig dubbele data bevat noemen we   dat redundantie (=overtolligheid)
  
• Als je op meer dan één plek dezelfde gegevens moet aanpassen bij   een wijziging, is er sprake van redundantie
  
• Een uitzondering zijn refererende sleutels
  
• Redundatie maakt een database minder betrouwbaar (integer)
  
• Het voorkomen van redundantie maakt je database consistenter.

Relationele databases

• Bij een goede relationele database hebben (vrijwel) alle tabellen   een duidelijke relatie met minimaal één andere tabel
• Een schema van een relationele database heet een ERD (Entity   Relationship Diagram)
  
• Een tabel wordt ook wel entiteit genoemd
  

Voorbeeld eenvoudig ERD

Een ERD van de eerder gebruikte
FastFood-database beschrijft de:

• Entiteiten (tabellen)
• onderlinge Relaties
  

Gekoppelde tabellen

Veel queries maken gebruikt van gekoppelde tabellen

Je kunt informatie uit gekoppelde tabellen samenvoegen met een query

Je gebruikt daarbij het INNER JOIN statement

Twee tabellen in een query

‘Geef bij elke bestelling de klantgegevens van de bijbehorende klant’

Resultaat INNER JOIN

‘Geef bij elke bestelling de klantgegevens van de bijbehorende klant’

Resultaat INNER JOIN

De koppeling maakt duidelijk dat Noa twee bestellingen heeft gedaan

Resultaat INNER JOIN

Dit is duidelijk omdat klant_id 10 (van Noa) bij 2 bestellingen voorkomt

Zelf proberen

Ga naar Fundament -> C5 -> 3. Verdieping: verder met SQL -> 3.2 Raadplegen van meer dan één tabel

En kies voor
SQL uitvoeren

3.2 Gekoppelde tabellen - vraag 1

'Geef een overzicht van de bestelnummers en datum/tijdstippen van de bestellingen die gedaan zijn door de klant met
het e-mailadres '[e-mail]'.

1. Bepaal het id van de klant
2. Koppel (JOIN) de tabellen met dit id
3. Toon de gevraagde kolommen

Tot hoeveel resultaten (rijen) leidde voorgaande query?

3.2 Gekoppelde tabellen - antwoord 1

Overzicht van bestelnummers en datum/tijdstippen van bestellingen gedaan zijn door klant '[e-mail]' - Deze klant heeft 7 bestellingen gedaan.

SELECT bestelling.id, datum, tijd

FROM klant

INNER JOIN bestelling ON bestelling.klant_id = klant.id

WHERE emailadres = '[e-mail]'

Koppel de tabellen met de kolom klant_id uit de tabel bestelling en de kolom id uit de tabel klant. Dit komt achter INNER JOIN te staan. Let op de volgorde! Eerst komt SELECT, daarna FROM en dan INNER JOIN. Pas hierna komt WHERE.

Meer dan twee tabellen in een query

‘Geef bij de bestelling met id 47 de bijbehorende producten en aantallen’

Leerdoel gehaald?

Je begrijpt wat een primaire sleutel en een vreemde sleutel zijn, kent de term redundantie, en weet wat een Entity Relationship Diagram is en hoe je deze kunt toepassen. Daarnaast kun je gegevens uit meerdere tabellen selecteren door ze te koppelen via een gemeenschappelijke kolom.

Maken: 2 & 3.1 t/m 3.2

• Bekijk: Fundament -> C5 -> 2. Structuur van een database 
• Bekijk: Fundament -> C5 -> 3. Verdieping: verder met SQL -> 3.1 t/m   3.2 
• Maak: 3.2 -> vraag 2, 3, 4 en 5.

Databases & SQL

Verder met SQL: Functies en keywords en groeperen

Leerdoel

Je kunt SQL-functies en keywords zoals COUNT, SUM en AVG en DISTINCT gebruiken en weet hoe je gegevens kunt groeperen voordat je deze in een functie toepast.

Rijen tellen - COUNT(*)

‘Hoeveel klanten staan er in de database?’

Waarden tellen - COUNT(<kolom>)

‘Hoeveel klanten hebben een achternaam?’

Dus COUNT(*) telt aantal rijen, COUNT(<kolom>) het aantal keer dat een unieke waarde voorkomt in een kolom

Waarden optellen - SUM(<kolom>)

‘Hoeveel producten zijn er totaal besteld van product 8?’

Functieoverzicht

Zelf proberen

Ga naar Fundament -> C5 -> 3. Verdieping: verder met SQL -> 3.3 Functies en keywords

En kies voor
SQL uitvoeren

3.3 Het duurste product - vraag 1

'Wat is de prijs van het duurste product?'

Wat is de prijs van het duurste product?

3.1 Het duurste product - antwoord 1

'Wat is de prijs van het duurste product?'

SELECT MAX(prijs) AS 'Prijs duurste product'

FROM product

Met de functie MAX krijg je de grootste kolomwaarde. 

Het keyword DISTINCT

Gebruik het keyword DISTINCT om dubbele waarden uit de resultaten te filteren.

Groeperen

‘Geef het aantal bestelde producten per bestelling’

Dit is het aantal zonder uitsplitsing op bestelling.

Groeperen op een bepaalde kolom

‘Geef het aantal bestelde producten per bestelling’

Dit is het aantal uitgesplitst per bestelling.

Zelf proberen

Ga naar Fundament -> C5 -> 3. Verdieping: verder met SQL -> 3.4 Groeperen

En kies voor
SQL uitvoeren

3.4 Bestellingen per klant - vraag 1

'Geef een overzicht waarin te zien is hoeveel bestellingen er bij ieder klantnummer horen.'

Hoeveel verschillende klantnummers hebben er bestellingen gedaan?

3.4 Bestellingen per klant - antwoord 1

'Geef een overzicht waarin te zien is hoeveel bestellingen er bij ieder klantnummer horen.'

SELECT klant_id, COUNT(*)

FROM bestelling

GROUP BY klant_id

Voor een overzicht met bestellingen per klant moet je groeperen op klant. Dit doe je door het klant_id te gebruiken, dat is voor iedere klant uniek. Het tellen van de bestellingen gaat met de functie COUNT, die de rijen telt. Iedere bestelling is immers een rij. 

Leerdoel gehaald?

Je kunt SQL-functies en keywords zoals COUNT, SUM en AVG en DISTINCT gebruiken en weet hoe je gegevens kunt groeperen voordat je deze in een functie toepast.

Maken: 3.3 t/m 3.4

• Bekijk: Fundament -> C5 -> 3. Verdieping: verder met SQL -> 3.3 t/m   3.4 
• Maak: 3.3 -> vraag 2, 3, 8, 9 en 13.
• Maak: 3.4 -> vraag 2, 4 en 5.

Databases & SQL

Verder met SQL: Voorwaarden aan groepen en subquery's

Leerdoel

Je kunt voorwaarden stellen aan een groepering met HAVING in plaats van WHERE. Ook kun je subquery's gebruiken en ken je het gebruik van operatoren zoals IN en NOT IN en logische operatoren zoals =, > en < binnen subquery's.

Voorwaarde aan gegroepeerde waarden

‘Welke bestellingen bestonden uit meer dan 20 producten?’

Voorwaarde aan gegroepeerde waarden

‘Welke bestellingen bestonden uit meer dan 20 producten?’

WHERE en HAVING tegelijk

‘Welke bestellingen uit 2018 bestonden uit meer dan 1 product?’

Zelf proberen

Ga naar Fundament -> C5 -> 3. Verdieping: verder met SQL -> 3.5 Voorwaarden aan groepen

En kies voor
SQL uitvoeren

3.5 Dubbele klantnamen - vraag 1

'Geef een overzicht van de klantnamen die meerdere keren voorkomen. Toon ook het aantal keer dat deze klantnaam voorkomt.'

Hoeveel klantnamen komen meerdere keren voor?

3.5 Dubbele klantnamen - antwoord 1

'Geef een overzicht van de klantnamen die meerdere keren voorkomen.'

SELECT naam, COUNT(*) AS aantal

FROM klant

GROUP BY naam

HAVING COUNT(*) > 1

In deze vraag gaat het om klantnamen. Dat moet je dus op groeperen. De voorwaarde is dat de namen meerdere keren voor moeten komen. Bij HAVING moet je dus rijen tellen met COUNT. De uitkomst hiervan moet groter zijn dan 1.

Resultaat van een query in een andere query gebruiken

‘Geef alleen de naam van het duurste product’

Als het resultaat uit meerdere rijen bestaat

‘Geef de namen van de producten die nog nooit zijn besteld’

Leerdoel gehaald?

Je kunt voorwaarden stellen aan een groepering met HAVING in plaats van WHERE. Ook kun je subquery's gebruiken en ken je het gebruik van operatoren zoals IN en NOT IN en logische operatoren zoals =, > en < binnen subquery's.

Maken: 3.5 t/m 3.6

• Bekijk: Fundament -> C5 -> 3. Verdieping: verder met SQL -> 3.5 t/m   3.5 
• Maak: 3.5 -> vraag 2 en 3.
• Maak: 3.4 -> vraag 2, 3 en 4.