Databases 1

1ste jaars IB studenten introdroductie RDBMS, phpmyadmin, databases en queries.
XAMPP, SELECT, COUNT(*), LIKE, AND/OR, MIN(), MAX(), JOIN, ORDER BY

XAMPP Installeren
1 Wat is een Database?
2 SQL - SELECT-FROM-WHERE
3 SELECT count(*)
4 Instructiefilmpjes (les 1,2,3)
5 Herhalingsoefeningen (les 1,2,3)
6 Sorteren
7 Join
8 Aggregaat functies
Instructiefilmpjes (les 6,7,8)
Herhaling (alles)
Student (test)database

XAMPP Installeren

In deze les leggen we uit hoe je XAMPP moet installeren. XAMPP is software voor de Mac en voor Windows waarin een RDMS (Database) en een webserver zit. Je kunt met deze software oefenen met het aanmaken en raadplegen van databases.

Download de software

Google of download de juiste versie van apachefriends en voer de installatie uit.

Kies de volgende opties om te installeren.

Voor de installatie blijven de rest van de opties op de standaard waarden staan.

Update

Als je al een keer eerder XAMPP had geïnstalleerd dan moet je de folder c:\xampp weggooien of beter nog een andere naam geven. Let op als je websites hebt ontwikkeld of databases hebt aangemaakt dan staan deze waarschijnlijk ergens in de folder c:\xampp. Als je deze folder dan weggooit, dan ben je dus ook jouw web sites en databases kwijt. Als je dit niet wilt dan zul je dus een back-up moeten maken. Kopieer hiervoor jouw web sites (html en php files) naar een andere plaats en maak met phpmyadmin een export van je databases.

De Installatie spreekt voor zich je kunt overal de standaard opties aanhouden.

Opstarten XAMPP

Als het goed is, is er tijdens de installatie een short cut gemaakt die heet XAMPP Control.

Deze shortcut voert C:\xampp\xampp-control.exe uit.

Druk op de eerste twee knopjes start naast Apache en MySQL. Hiermee start je de webserver en de database op. Als alles goed is dan zie je het volgende.

Port conflict

Het kan zijn dat je foutmeldingen ziet, lees die goed. Zie je iets over port conflict op poort 443? Dat wordt mogelijk door VM player of door Steam veroorzaakt. Je kunt twee dingen doen. Je kunt VM player server (of Steam) stoppen of je kunt het poortnummer van de webserver veranderen. Dit laatste doe je als volgt.

Druk op de knop Config op de Apache regel en selecteer de Apache (HTTP-SSL.conf) optie. Er opent een window. Zoek het stukje dat er als volgt uitziet:

## When we also provide SSL we have to listen to the 
# standard HTTP port (see above) and to the HTTPS port
#
Listen 443

en verander dat in

# When we also provide SSL we have to listen to the 
# standard HTTP port (see above) and to the HTTPS port
#
Listen 1443

Er wordt dus een 1 voor de 443 geplaatst. Sluit de file en bewaar deze. Start de webserver opnieuw op.

1 Wat is een Database?

Verschillende soorten databases

Flat file (txt of cvs)
Hierarchisch (bijvoorbeeld file systeem)
Netwerk
Relationeel RDBMS
Object database

De meest gebruikte vorm is het Relationele Database Management Systeem, ook wel RDBMS. Voorbeelden van een RDBMS zijn: MySQL, MariaDB, Oracle, Microsoft SQL Server, SQLIte en PostgreSQL.

RDBMS

Een RDBMS is gebouwd op basis van relaties tussen de verschillende data eilandjes (tables).

SQL, Structured Query language is de taal waarmee je met de database praat in een RDBMS

Een RDBMS bestaat uit de volgende fysieke (hoofd)onderdelen:

databases
Dit is een verzameling tabellen die logisch bij elkaar horen.
tables
Dit is een groepje gegevens die bij iets horen, bijvoorbeeld bij de table persoon of klaslokaal.
columns (fields)
Dit zijn de gevens die je van een tabel wilt vastleggen, bijvoorbeeld de naam van een persoon of het nummer van een klaslokaal.
rows (records)
Dit zijn de gegevens zelf. Bijvoorbeeld alle personen in de table persoon of alle klaslokalen in de table klaslokaal.

Een raltrelatie ie in een RDBMS wordt gelegd door verschillende tabellen met elkaar te verbinden door middel van keys (veelal ID's).

In het plaatje zien we hoe tabellen, regels en kolommen (tables, rows and columns) in een database zich tot elkaar verhouden. We kunnen ook zien dat de twee (voorbeeld) tabellen een relatie met elkaar hebben. Je kunt bijvoorbeeld zien dat Jane in Amsterdam woont. De plaatsnaam is namelijk via het ID gekoppeld aan de tabel Personen.

Database World

We gaan de database World maken en de tabellen en regels importeren.

In XAMPP start Apache en MySQL op en ga in je browser naar 127.0.0.1/phpmyadmin.

Aan de linkerkant keizen we new of nieuw. Hiermee kunnen we een nieuwe database aanmaken. Maak de database World aan.

Kies het tabje import en importeer de file world.sql

In de volgende lessen gaan we gebruik maken van deze database.

Hieronder is een ERD (Entiteit Realtie Diagram) van de database weergegeven. In dit ERD kun je de relaties van de verschillende tabellen zien. Deze zijn belangrijk als we gegevens uit twee tabellen willen combineren.

2 SQL - SELECT-FROM-WHERE

In de eerste serie lessen gaan we verschillende queries maken die betrekking hebben op één tabel.

SQL

SQL betekent Structured Query Language en is de taal om een database te beheren. Met SQL kan je

De database structuur aanpassen
De database inhoude veranderen
De database raadplegen

Database raadplegen

De SQL statements waarmee we de database kunnen raadplegen worden het meest gebruikt en de eenvoudige structuur van een raadpleeg statement is:

SELECT <kolomnaam<, <kolomnaam>, ....
FROM <tabelnaam>
WHERE <vergelijking>

Een vergelijking lijkt op JavaSctipt vergelijkingen, een paar voorbeelden:

WHERE naam = 'Netherlands'	kolom naam moet 'Netherlands'bevatten.
WHERE leeftijd > 18	kolom leeftijd moet groter dan 18 zijn (dus 19,20,21,....)
WHERE prijs < 4.99	kolom prijs is kleiner dan 4.99
WHERE prijs > 5 and prijs <10	kolom prijs is groter dan 5 en kleiner dan 10, en ligt dus tussen 5 en 10 in.
WHERE prijs >=5 and prijs <=10	kolom prijs is minimaal 5 en maximaal 10
WHERE continent='Europe' or continent='Africa'	kolom continent is Europe of Africa
WHERE naam like 'Ned%'	kolom naam begint met een Ned (% betekent any characters)
WHERE naam like '%n%'	kolom naam bevat een n
WHERE naam like '%e'	kolom naam eindigt op een e
WHERE naam like 'A%' or naam like 'B%'	kolom naam begint met een A of met een B
WHERE naame like 'A%' and naam like 'B%'	kolom naam begint met een A en met een B; wat klopt hier niet?

We gaan kijken naar de Country tabel, deze heeft de volgende structuur.

TABEL country
Kolomnaam	Beschrijving	Voorbeeld
Code	Primary Key (Country Code)	NLD
Name	Naam van het land	Netherlands
Continent	Continent	Europe
Region	Regio	Western Europe
SurfaceArea	Oppervlakte (in KM²)	41526.00
IndepYear	Jaar van onafhankelijkheid	1581
Population	Bevolkingsgrootte, inwoneraantal	15864000
LIfeExpectancy	Levensverwachting	78.3
GNP	Bruto Nationaal Product	371362.00
GNPOld	Oud Bruto Nationaal Product	360478.00
LocalName	Lokale naam	Nederland
GovernmentForm	Regeringsvorm	Constitutional Monarchy
HeadOfState	'Baas' van het land	Alexander
Capital	Foreign Key naar City	5
Code2	Officiële landsafkorting (bijv NL)	2

We gaan nu queries uitvoeren op de database world in phpmysql.

Ga naar phpmysql en selecteer de World database (links) kies daarna het tabje SQL. Voer de volgende query uit.

SELECT Name, Continent
FROM country
WHERE name like 'F%'

Wat zie je?

Opdrachten

Nu mag jij queries bedenken en uitvoeren. Kopieer de queries in een text file en stuur die via Teams op.

Maak een query waarmee je alle landennamen laat zien waarvan de naam van het land met een B begint.
Schrijf de query op je antwoordenblad.
Maak een query waarmee je alle landennamen en de continenten laat zien en waarvan de naam van het land met een B begint.
Schrijf de query op je antwoordenblad.
Maak een query waarmee je alle landennamen, de continenten en het aantal inwoners laat zien en waarvan de naam van het land met een B begint.
Schrijf de query op je antwoordenblad.
In welke landen is de levensverwachting (LIfeExpectancy) lager dan 40.
Schrijf de query op je antwoordenblad.
Wat denk je zijn er landen waar geen mensen wonen? Maak en query en selecteer alle landen waar geen mensen wonen. Kijk goed in de tabel welke kolommen je kan gebruiken.
Schrijf de query op je antwoordenblad.
Welke landen hebben meer dan 0 en minder dan 100 inwoners?
Schrijf de query op je antwoordenblad.
Maak een query die alle landen (naam en lokale name) laat zien die op het Continent Europa liggen.
Schrijf de query op je antwoordenblad.
Maak een query die alle landen en hun regeringsvorm laat zien die op het Continent Europa liggen.
Schrijf de query op je antwoordenblad.
Hoeveel landen hebben een inwoneraantal meer dan 1 000 000 000 (dat is 1 miljard mensen; dat is 1000-miljoen)? Let op als je dit getal intypt dat je de spaties weglaat. Schrijf de query die je hebt gebruikt op je antwoordenblad.
De oppervlakte van de landen wordt in km² gemeten. Dat is een vierkante kilometer. Dat is een stuk land van 1000 meter in het vierkant. Een land met een oppervlakte van 1 km² is dus heel klein; als je doorloopt, kun je er in 10 minuten dwars doorheen lopen. Zijn er landen met een oppervlakte minder dan 1 km²? Maak een query om dat te bepalen.
Schrijf de query die je hebt gebruikt op je antwoordenblad.

3 SELECT count(*)

SELECT count() en SELECT

In de select kunnen we ook alleen het aantal rijen dat we terugkrijgen tellen. Stel we willen weten hoeveel lander er in de tabel Country staan dan kan met de volgende SQL Query.

SELECT count(*)
FROM Country

Als je alle kolommen wilt afdrukken dan kun je ook alleen * gebruiken:

SELECT *
FROM Country

Probeer nu zelf:

SELECT count(*)
FROM country
WHERE name like 'F%'

SELECT *
FROM country
WHERE name like 'F%'

Zie je wat het verschil is?

Als je een SELECT count(*) gebruikt dan wil je dus een aantal weten. Je krijgt dan alleen een getal. Bijvoorbeeld alle leerlingen die een voldoende hebben gehaald.

SELECT count(*)
FROM leerlingen
WHERE cijfer >= 5.5

De uitkomst van deze query is bijvoorbeeld 10. Er zijn in dat geval 10 leerlingen die een 5.5 of hoger hebben gehaald. Na de count(*) kan je geen andere kolommen namen meer opvragen.

SELECT count(*), name (is FOUT!)

Kan dus niet want je kan niet alleen het aantal van iets afdrukken en dan de name ook nog afdrukken. Je wilt of de name zien of alleen het aantal.

count(*) staat altijd alleen en eenzaam na de SELECT.

(Later zul je zien dat er op deze regel uitzonderingen bestaan, maar dat komt pas als we meer ingewikkelde queries gaan maken).

Opdrachten

(schrijf van elke vraag de query die je hebt gebruikt op en maak gebruik van count(*) )

Hoeveel landen zijn waarvan de naam begint met een N?
Hoeveel landen hebben een oppervlakte van minder dan 100 km²?
Hoeveel landen hebben een inwoneraantal meer dan 0 en minder dan 1000 inwoners?
Hoeveel landen liggen er op het continent 'Europe'?
Hoeveel landen liggen er op het continent 'South America' met meer dan 10 miljoen inwoners?
Hoeveel landen liggen er op het continent 'Africa' met meer dan 10 miljoen inwoners?
Hoeveel landen liggen er op het continent 'Europe' die kleiner zijn in oppervlakte ('SurfaceArea') dan Nederland?
Hoeveel landen liggen er op het continent 'Europe' die groter zijn in inwoneraantal ('Population') dan Nederland?

4 Instructiefilmpjes (les 1,2,3)

Instructiefilmpjes op YouTube over les 1, 2 en 3

Hieronder staan links naar drie korte instructiefilmpjes waarin de belangrijkste zaken van les 1,2 en 3 nog een keer worden uitgelegd.

5 Herhalingsoefeningen (les 1,2,3)

Hieronder ter voorbereiding op de eerste test nog enkele oefeningen. Kijk nog een keer naar de filmpjes uit de vorige les. Daarin worden de belangrijkste- en moeilijkste dingen nog een keer kort uitgelegd.

Kijk voor je hier aan begint de instructiefilmpjes uit de les 4 door!

Lees goed wanneer je alleen het aantal moet afdrukken gebruik dan count(*), dit wordt ook in de filmpjes nog een keer uitgelegd.

De vragen gaan in deze oefening over de country tabel uit de world database.

Maak één query waarin je het inwoneraantal van Frankrijk en Spanje laat zien.
Test de query uit en zet de juiste query op je antwoordenblad.
Maak één query waarin je het aantal landen laat zien (dus alleen een getal) dat tussen de 1 en 2 miljoen inwoners heeft. Dus meer dan 1 miljoen en minder dan 2 miljoen.
Test de query uit en zet de juiste query op je antwoordenblad.
Maak één query waarin je het aantal landen laat zien (dus alleen een getal) dat tussen de 1 en 2 miljoen inwoners heeft en dat op het continent 'Africa' ligt.
Test de query uit en zet de juiste query op je antwoordenblad.
Maak een query waarin je alle landen waarvan de naam met een 'C' begint, afdrukt.
Test de query uit en zet de juiste query op je antwoordenblad.
Maak een query waarin je het aantal landen (dus alleen getal) laat zien waarvan de naam met een A, of een B of een C begint.
Test de query uit en zet de juiste query op je antwoordenblad.
Welk continent heeft de meeste landen, het continent 'Africa' of 'Asia'?
Schrijf het antwoord en de query's die je hebt gebruikt om dit te bepalen op je antwoordenblad.
Welk continent heeft meer landen waarvan de eerste letter een A is, 'Europe' of 'Africa'?
Schrijf het antwoord en de query's die je hebt gebruikt om dit te bepalen op je antwoordenblad.

6 Sorteren

In deze les leren we hoe we het resultaat van een query kunnen sorteren. Tot nu toe hebben we geoefend met de table country (land). We gaan nu ook naar de table city (stad) kijken.

In les 1 hebben we de World database geïmporteerd. In deze World database zitten drie tabellen. In phpmyadmin kun je zien welke tabellen dit zijn.

In deze les gaan we kijken naar de tabel country.

TABEL city
Kolomnaam	Beschrijving	Voorbeeld
CountryCode	Foreign Key naar table country	NLD
District	Provincie	Gelderland
ID	Primary key	1
Name	Naam van de stad	Amsterdam
Population	Bevolkingsgrootte, inwoneraantal	153491

Met de foreign key Country Code wordt de table city gekoppeld aan de table country. Hierover gaat de volgende les. We gaan nu eerst kijken naar de tabel city.

Sorteren

Als laatste regel in een query kunnen we aangeven dat de regels die een query teruggeeft worden gesorteerd. Dat gaat als volgt:

ORDER BY <kolomnaam> ASC
Dit sorteert op de kolomnaam <kolomnaam> in oplopende volgorde, dus van laag naar hoog.

ORDER BY <kolomnaam> DESC
Dit sorteert op de kolomnaam <kolomnaam> in aflopende volgorde, dus van hoog naar laag.

Voorbeeld:

SELECT Name, Population
FROM country
ORDER BY Population DESC

Deze query drukt alle landen af gesorteerd op inwoneraantal (population) van hoog naar laag.

Vergeet niet de video (die hierboven staat) te bekijken voordat je aan de opgave begint. Veel antwoorden staan namelijk in de video.

Opgave 1

Met behulp van de query hierboven kun je bepalen welk land de meeste inwoners heeft. Voer de query uit en schijf op je antwoordenblad de naam van het land met de meeste inwoners.

Opgave 2

Welk land heeft de minste inwoners (0 niet meegeteld).

Tip: moet je oplopend of aflopend sorteren om te bepalen? Als je dit niet weet probeer dan beide methodes.

Schrijf de query op die je hebt gebruikt om dit te bepalen op je antwoordenblad.