Als je bepaalde rijen wilt selecteren die voldoen aan één of meerdere voorwaarden, dan gebruik je WHERE.

In het volgende voorbeeld maken we een lijstje van de Klanten die in "Spanje" wonen.

De query wordt:

Select *
From Customer
Where Country = 'Spain';

De basis syntax voor de query is:

Select <kolommen>
From <tabel>
Where <voorwaarde>;

In de voorwaarde zit altijd een operator, je kunt de volgende operatoren in SQL gebruiken:

2.4 Oefening

Welke klanten hebben de voornaam "Mark"? (geef de voornaam en de achternaam)

Met het commando IN kun je hetzelfde bereiken als met OR maar de query wordt dan korter.

Als je de klanten wilt selecteren die in France, Canada of Netherlands wonen, dan kun je als voorwaarde gebruiken:

WHERE Country="France" OR Country="Canada" OR Country="Netherlands"

Maar het kan ook met de volgende voorwaarde:

WHERE Country IN ("France", "Canada", "Netherlands")

De volledige query wordt dan als volgt:

SELECT * 
FROM Customer; 
WHERE Country IN ("France", "Canada", "Netherlands")

Met BETWEEN moet de kolomwaarde tussen twee waarden liggen.

Voorbeeld:

select * from Invoice where Total BETWEEN 6 and 10;

NOT

Met NOT kun je de klanten krijgen die niet aan een bepaalde voorwaarde voldoen.

Stel dat je alle klanten wilt hebben die niet in Canada wonen.

Dat kan m.b.v. de volgende query:

SELECT * 
FROM Customer; 
WHERE not Country = "Canada"

Je kunt hetzelfde lijstje krijgen m.b.v. deze query:

SELECT * 
FROM Customer; 
WHERE Country <> "Canada"

Als er aan meerdere voorwaarden moet worden voldaan kun je gebruik maken van AND en OR.

Als je bijvoorbeeld alle facturen wilt met een totaal bedrag tussen de 6 en 10 euro. Krijg je de volgende query:

select * 
from Invoice 
where Total > 6 AND Total < 10;

Nog een voorbeeld:

select * 
from Customer 
where Country = 'USA' OR City = 'New York';

Combineren van AND en OR kan natuurlijk ook:

Stel nu deze:

select * 
from customer 
where firstname = 'Mark' or firstname = 'Frank' and Country = 'USA';

Wat betekent dit? Krijg ik dan:

1. Alle klanten die mark en Frank heten die in America wonen

2. Krijg ik alle klanten die mark heten en alle klanten die frank en in america wonen.

Dat 2de dus.

Als we dat niet willen moeten we even haakjes plaatsen. Dan krijg je dus dit:

select * 
from customer 
where (firstname = 'Mark' or firstname = 'Frank') and Country = 'USA';

2.5 Oefening

Geef alle klanten die uit amerika komen en wonen in de stad new york of mounten view

Hieronder zie de planner voor VWO 5, de tweede periode

Een eenvoudige selectie met SELECT

Stel dat de beheerder van de database een lijst wil hebben met voornaam, achternaam en woonplaats van de klanten die lid zijn van de bibliotheek. Dit kan in SQL vrij eenvoudig gerealiseerd worden met de volgende query. Probeer maar eens uit! Ga naar de database op repl.it. Voor het volgende commando uit binnen repl.it:

select FirstName, LastName, City 
from Customer;

Krijg je iets wat hier op lijkt:

Luís|Gonçalves|São José dos Campos
Leonie|Köhler|Stuttgart
François|Tremblay|Montréal
Bjørn|Hansen|Oslo
František|Wichterlová|Prague
Helena|Holý|Prague
Astrid|Gruber|Vienne
Daan|Peeters|Brussels
Kara|Nielsen|Copenhagen
Eduardo|Martins|São Paulo
Alexandre|Rocha|São Paulo
Roberto|Almeida|Rio de Janeiro
...

Dan gaat het goed!

Uitleg: 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 (;). Een SQL-vraag noem je een query.

Je zou er ook voor kunnen kiezen om alle kolommen uit de Customer tabel te willen zien. Dan kun je natuurlijk alle kolommen achter SELECT zetten, maar het kan gelukkig veel korter met behulp van *. Zie:

SELECT * 
FROM Customer;

2.1 Oefening: Selecteer de voornaam, achternaam en telefoon van alle Klanten.

SQL is een querytaal is een soort programmeertaaltje waarmee je vragen kan stellen aan de database. De afkorting SQL staat voor Structured Query Language. Wat kun je nou met SQL?

Zie de volgende opsomming:

• Met SQL kun je gegevens opvragen van een database

• Met SQL kun je gegevens toevoegen aan een database

• Met SQL kun je gegevens wijzigen in een database

• Met SQL kun je gegevens verwijderen uit een database

• Met SQL kun je nieuwe databases aanmaken;

• Met SQL kun je tabellen aanmaken en aan een database toevoegen;

• en nog veel meer ..

Een voorbeeld van een eenvoudige query zou kunnen zijn: Geef mij een lijst van alle mannelijke leden die in team B zitten. De bijbehorende query zou er als volgt uit kunnen zien:

select *
from leden
where geslacht = 'm' and team = 'b';

In deze cursus wordt de basis van de taal SQL stap voor stap uitgelegd. Je zult ook opdrachten moeten maken. Deze opdrachten kun je uitvoeren binnen repl.it.

Het is belangrijk dat je goed oefend met de opgaven, op de toets krijg je alleen maar van dit type vragen.

LIKE is een krachtige operator. Met LIKE kun je selecteren op gedeelten van de tekst.

Stel dat je een lijstje wilt van de klanten waarvan de naam met een D begint. Dat kan met de volgende query:

SELECT * 
FROM Customer
WHERE Firstname like 'D%';

Je ziet dat er na LIKE staat: 'D%'

Het % teken is een zogenaamde wildcard en staat voor een willekeurig aantal tekens.

Het betekent dus dat de naam met een D moet beginnen, en wat er achter staat doet er niet toe, dat mag alles zijn.

Als je alle namen en hun woonplaatsen wilt hebben van mensen die een dubbele l (ll) in hun naam hebben, dan gebruik je de volgende query:

SELECT Firstname, City
FROM Customer 
WHERE firstname LIKE  '%ll%'

In combinatie met LIKE kun je nog een andere wildcard gebruiken, namelijk het teken _ , de underscore. Dat teken staat voor één willekeurig teken (en een procentteken (%) staat voor meerdere willekeurige tekens (dat kan ook één of nul zijn).

Stel dat we alle namen willen hebben van de klanten waarvan de naam uit vier letters bestaat. De query wordt dan:

SELECT Firstname
FROM Customer 
WHERE firstname LIKE  '____'

We kunnen de beide wildcards ook combineren. Stel dat we de namen van de klanten willen hebben waarvan de voorlaatste letter een e is.

Die krijg je m.b.v. de volgende query:

SELECT Firstname
FROM Customer 
WHERE firstname LIKE  '%e_'

2.6 Oefening

Geef de voornaam, achternaam van alle klanten waarvan de eerste letter van de voornaam met een a, e, i, o of u begint.

Als je wilt controleren m.b.v. SQL of een kolom is ingevuld dan gebruik je IS NULL

Je kunt bijvoorbeeld de volgende query uitproberen:

select * 
from customer 
where phone is null;

Het resultaat is een lijst van klanten waarvan geen telefoonnummer in de database is geregistreerd.

Je zou ook het omgekeerde kunnen bevragen: dus alle leerlingen die wel een telefoonnummer hebben. Dat kan zo:

select * 
from customer 
where phone is not null;

2.7 Oefening

select CustomerId, Fax 
from Customer 
where fax is null;

Je kunt het aantal rijen dat wordt afgedrukt beperken m.b.v. LIMIT.

Stel dat je de top 2 jongste werknemers uit de database wilt. Je zou dan de volgende query kunnen opstellen.

select Firstname, Lastname, BirthDate 
from Employee 
order by BirthDate Desc 
LIMIT 3;

LIMIT 3 geeft aan dat de query moet stoppen na de derde rij.

Stel dat je de drie mensen wilt hebben, die wat hun geboortedatum betreft op de plaatsen 3, 4 en 5 staan. Dus de twee hoogsten niet, maar de drie die daarop volgen. Dat kan met de volgende query:

select Firstname, Lastname, BirthDate 
from Employee 
order by BirthDate Desc 
LIMIT 2,3;

Je ziet dat er nu na LIMIT 2,3 staat; dat betekent dat er wordt begonnen met rij nummer 2 (dat is de derde rij want er wordt begonnen met tellen bij 0, de eerste rij heeft nummer 0 en de tweede rij heeft nummer 1), en er worden 3 rijen afgedrukt.

Er is een mooie database die wij kunnen gebruiken voor om oefening mee te gaan maken. Ook wordt deze database gebruikt om de toets die jij gaat krijgen over dit onderwerp.

De betreffende database is te bekijken op gihub.

Hieronder het datamodel van de database, deze ga je nog veel gebruiken. Je hoeft er nu nog niet echt naar te kijken.

Dit is een database die veel wordt gebruikt voor cursusen. Ik heb voor ons deze database al even op repl.it gezet. Als je inlogd met je account kun je mijn versie dupliseren en zo heb je een 'eigen' versie van de database.

De werkomgeving klaar maken

1. Log in op repl.it met je eigen account.

2. Ga naar de volgende repl.it: repl.it

3. Druk op de Fork knop 4. Druk op de Run knop

5. Voer het volgende sql commando even uit om de testen of het werk:

select * from Artist LIMIT 10;

6. Als de output hierop - lijkt werkt het!

> select * from Artist LIMIT 10;
1|AC/DC
2|Accept
3|Aerosmith
4|Alanis Morissette
5|Alice In Chains
6|Antônio Carlos Jobim
7|Apocalyptica
8|Audioslave
9|BackBeat
10|Billy Cobham

Test deze 2 commando's even die zijn super handig!

>.schema Customer

Dan krijg je de velden in de Tabel.

CREATE TABLE [Customer]
(
    [CustomerId] INTEGER  NOT NULL,
    [FirstName] NVARCHAR(40)  NOT NULL,
    [LastName] NVARCHAR(20)  NOT NULL,
    [Company] NVARCHAR(80),
    [Address] NVARCHAR(70),
    [City] NVARCHAR(40),
    [State] NVARCHAR(40),
    [Country] NVARCHAR(40),
    [PostalCode] NVARCHAR(10),
    [Phone] NVARCHAR(24),
    [Fax] NVARCHAR(24),
    [Email] NVARCHAR(60)  NOT NULL,
    [SupportRepId] INTEGER,
    CONSTRAINT [PK_Customer] PRIMARY KEY  ([CustomerId]),
    FOREIGN KEY ([SupportRepId]) REFERENCES [Employee] ([EmployeeId]) 
        ON DELETE NO ACTION ON UPDATE NO ACTION
);
CREATE INDEX [IFK_CustomerSupportRepId] ON [Customer] ([SupportRepId]);

.tables

Dan krijg je een overzicht van alle tabellen die in de database zijn.

Album          Employee       InvoiceLine    PlaylistTrack
Artist         Genre          MediaType      Track        
Customer       Invoice        Playlist     

Cheat Scheet

We leven in een informatiemaatschappij. Dat betekent dat informatie een steeds belangrijkere rol neemt in de maatschappij. Het creëren, verspreiden en manipuleren van gegevens is een uiterst belangrijke hedendaagse activiteit. Informatie wordt gegenereerd door smartphones, computers, sensoren, de kassa van een supermarkt, de docent die jouw cijfers invoert en nog veel en veel meer. Met de komst van het Internet is de hoeveelheid gegevens grandioos gestegen. Kijk maar eens op de volgende site waar je wat live statistieken van het internet kan zien: http://www.internetlivestats.com

Mensen vragen zich wel eens af hoe groot het internet is. Via een snelle zoektocht op Google blijkt dat de vier grote bedrijven Microsoft, Google, Amazon en Facebook gezamenlijk ongeveer 1200 petabytes aan data bewaren. Dat alleen al is zo onvoorstelbaar veel. 1 petabyte is namelijk 13.3 jaar lang HD-TV video! Je kunt alle geschreven teksten in de menselijke geschiedenis opslaan in 20 petabytes! Zie ook het plaatje hieronder:

Maar hoe sla je die gegevens op? En hoe je zorg je ervoor dat je eenvoudig en doelgericht naar specifieke gegevens kan zoeken? Dit is een vaardigheid die met de dag belangrijker wordt.

Informatie kan digitaal opgeslagen worden op verschillende manieren. Een van die manieren is door gebruik te maken van een relationele database.

Een relationele database bestaat uit tabellen die onderling aan elkaar gerelateerd zijn. Hieronder vind je een diagram waarin drie tabellen zijn afgebeeld. Het gaat om LEDEN, TEAMS en WEDSTRIJDEN:

De tabel LEDEN bestaat uit zichzelf weer uit de kolommen lidnr, voornaam, tussenv, achternaam et cetera. De pijltjes die van de ene tabel naar de andere tabel wijzen, geven aan dat er een relatie is tussen de verschillende tabellen. Zo geeft het pijltje dat van aanvoerder naar lidnr wijst aan dat “een aanvoerder van een team, een bestaand lid moet zijn in de leden tabel”.

Het diagram wordt ook een strokendiagram genoemd. Het bevat nog meer informatie. De tabel bestaat uit een aantal regels. Je wilt in een tabel geen dubbele regels hebben. Bij de velden lidnr en teamnr staat een pijl. Die geven aan dat de regels uniek zijn om deze criteria. Zo zie je in de tabel ‘WEDSTRIJDEN’ dat de pijl over twee velden gaat ‘teamnr’ en ‘datum’; de combinatie van deze twee maakt een unieke regel.

De pijlen tussen de tabellen geeft ook informatie: Een team kan meerdere wedstrijden spelen, maar elke wedstrijd hoort maar bij één team. Zo zie je ook dat een team uit meerdere leden bestaat (logisch), maar dat een lid maar bij één team hoort. De aanvoerder van elk team is een lid, maar een lid kan meerdere keren aanvoerder (bij verschillende teams) zijn.

Dit houdt dan ook in dat een aanvoerder niet hoeft te spelen.

De queries die we tot nu toe hebben gemaakt leveren lijsten die niet gesorteerd zijn, terwijl dat best handig kan zijn. Willen we een lijst dat alfabetisch gesorteerd is op voornaam, achternaam, woonplaats of misschien wel een andere kolom, dan kan je het resultaat sorteren m.b.v. de clausule ORDER BY. Wanneer de beheerder vraagt om de telefoonlijst van alle klanten op achternaam te sorteren, dan kan dat bijvoorbeeld met de volgende query:

Wanneer je de lijst met telefoonnummers wilt sorteren op achternaam, kan het een probleem worden als sommige achternamen meerdere keren voorkomen. In dat geval kun je de tabel op twee kolommen sorteren:

In beide voorbeelden wordt de tabel oplopend gesorteerd: teksten volgens de ASCII-tabel,

getallen en datums van klein naar groot. Het is ook mogelijk om de volgorde om te keren: aflopend sorteren. De Engelse termen daarvoor zijn ‘ascending’ en ‘descending’ (ASC en DESC).

Wanneer je er niets bij zet, wordt automatisch oplopend gesorteerd. Om aflopend te sorteren moet je dat achter de kolom in ORDER BY zetten.

2.3 Oefening

Maak een lijst van alle klanten (voornaam, achternaam en land) gesorteerd op land en binnen het land op achternaam.

Als de beheerder van de database een lijst wil hebben van de woonplaatsen, dan zou je de volgende query kunnen opstellen:

Een probleem is echter wel, dat je sommige woonplaatsen meerdere keren in de lijst terugziet. Dat is eigenlijk overbodig, want de beheerder wil alleen weten uit welke steden zijn klanten komen. Om unieke rijen te krijgen kun je het beste gebruik maken van DISTINCT. Hiermee verwijder je alle dubbele waarden uit het resultaat. De query ziet er als volgt uit:

2.2 Oefening: Geef een lijst van alle landen waar onze klanten vandaan komen

e volgende opdrachten maak je in repl.ot. Je bedenkt voor elke informatiebehoefte een query en geeft die query als antwoord op de vraag.

1. Welke klanten komen uit 'the Netherlands'?

2. Geef een (unieke) lijst van alle landen waar onze klanten zitten?

3. Geef een lijst van alle Tracks die langer duren dan 200000 ms.

4. Sorteer bovenstaande lijst van laag naar hoog.

5. Wat zijn track namen van de de 3 langste tracks

Opdracht 4.1

Maak een lijstje met aantallen employees per plaats

Opdracht 4.2

Hoeveel tracks zijn er per Genre aanwezig

Opdracht 4.3

Bij welke Genre zijn er meer dan 10 aanwezig

Opdracht 4.4

Geef het gemiddelde lengte van een track per Genre, maar dan alleen als de lenge hoger is dan

Opdracht 5.5

Maak een overzicht van het aantal tracks per genreid en de totale lengte (in ms) per GenreID.

Voorbeeld:

1|1297|368231326 2|130|37928199 3|374|115846292 4|332|77805478 5|12|1615722

Graag onderstaande vragen oplossen met functie en niet met 'order by' en Limit

Opdracht 3.1

Hoeveel tracks hebben wij in de onze database?

Opdracht 3.2

Wat is de langste track in de database.

Opdracht 3.3

Wat is de kortste track in de database?:

opdracht 3.4

Hoeveel tracks hebben we met het GerneID nr 10

opdracht 3.5

Hoeveel tracks zijn langer dan de gemiddelde lengte?

Opdracht 4.1

Maak een lijstje met aantallen employees per plaats

Opdracht 4.2

Hoeveel tracks zijn er per Genre aanwezig

Opdracht 4.3

Bij welke Genre zijn er meer dan 10 aanwezig

Opdracht 4.4

Geef het gemiddelde lengte van een track per Genre, maar dan alleen als de lenge hoger is dan

Opdracht 5.5

Maak een overzicht van het aantal tracks per genreid en de totale lengte (in ms) per GenreID.

Voorbeeld:

1|1297|368231326 2|130|37928199 3|374|115846292 4|332|77805478 5|12|1615722

In de vorige paragraaf heb je gezien dat je het totaal aantal rijen van een tabel kunt laten tellen en afdrukken.

En je kunt de som van alle getallen in een kolom optellen, enz.

Maar je kunt de tabel ook indelen in groepjes van dezelfde soort, en de aantallen in die groepjes laten tellen en afdrukken (of optellen, of het gemiddelde bepalen, enz).

Stel dat je een lijstje wilt van alle landen, met per land het aantal klanten dat daar woont.

Dat kan met de volgende sql-query:

Nog een voorbeeldje, heb je een idee wat je hier doet?

HAVING

Stel dat je een lijstje wilt van de Tracks en de totale lengte per GenreID, maar dan alleen de tracks die een totale lengte van meer dan 20000 MS hebben.

Bij een voorwaarde die betrekking heeft op een functie, waarbij group by gebruikt wordt, dan moet je gebruik maken van HAVING.

De query moet dan als volgt worden geformuleerd:

Oefening 2.1

Oefening 2.2

Oefening 2.3

Oefening 2.4

Oefening 2.5

Oefening 2.6

Oefening 2.7

Antwoord 2.1

Antwoord 2.2

Antwoord 2.3

Antwoord 2.4

Antwoord 2.5

2. Sorteer bovenstaande lijst van laag naar hoog.

3. Wat zijn track namen van de de 3 langste tracks

Stel dat je de naam van het oudste medewerker, wilt laten afdrukken, dan kan dat met een subquery.

Die gaat als volgt:

select Firstname, Lastname 
from Employee
where BirthDate = (
    select min(BirthDate) 
    from Employee);

Als deze query wordt uitgevoerd dan wordt eerst de subquery (de query tussen haakjes, dus:

select min(BirthDate) 
from Employee;

Als je die uitvoert verschrijt er: 1947-09-19 00:00:00

Daarna wordt de hoofdquery uitgevoerd, en in plaats van de subquery wordt 1947-09-19 00:00:00 ingevuld.

Er staat dan dus eigenlijk:

select Firstname, Lastname 
from Employee
where BirthDate = "1947-09-19 00:00:00";

Subquery met IN

Stel dat je al de tracknamen van alle leerlingen die een boek hebben gereserveerd wilt afdrukken.

Dan kan dat door een join te gebruiken:

select * 
from InvoiceLine as I, Track as T 
where I.TrackID = T.TrackID;

Let op dat hier met AS een afkorting maken voor de join!

Maar dit kan dus ook met een subquery:

Select *
from InvoiceLine
Where TrackID IN (
    select TrackID
    from Track);

Omdat er nu uit een lijstje gekozen worden moet je IN gebruiken, en geen =

Je mag alleen = gebruiken als je er zeker van bent dat het resultaat van de subquery maar één exemplaar oplevert!

Vraag 5.1

Maak een lijst van alle artisten met hun albums

select Artist.Name, Album.Title 
from Artist, Album 
Where Artist.ArtistID = Album.ArtistID

Vraag 5.2

Maak een overzicht van de artisten met het aantal albums

select Artist.Name, count(Album.Title)
from Artist, Album 
Where Artist.ArtistID = Album.ArtistID
group by Artist.ArtistId;

Vraag 5.3

Maak een lijst van de artisten met hun albums en hun tracks

select Artist.Name, Track.name
from Artist, Album, Track
Where Artist.ArtistID = Album.ArtistID AND
Track.AlbumID = Album.AlbumID;

Vraag 5.4

Maak een overzicht van de artisten en hun aantal tracks

select Artist.Name, count(Track.trackid)
from Artist, Album, Track
Where Artist.ArtistId = Album.ArtistID AND
Track.AlbumID = Album.AlbumID
group by Artist.ArtistId;

Vraag 5.5

Geef de top 3 artisten die de meeste tracks in onze database heeft

select Artist.Name, count(Track.trackid)
from Artist, Album, Track
Where Artist.ArtistId = Album.ArtistID AND
Track.AlbumID = Album.AlbumID
group by Artist.ArtistId
order by count(Track.Trackid) desc
Limit 3;

Vraag 5.6

Maak een overzicht van alle tracks van de genre 'Opera;

Select Track.Name, Genre.Name
From Track, Genre
Where Track.GenreID = Genre.GenreID and
Genre.name = 'Opera';

Vraag 5.6

Maak een overzicht per genre van het aantal track en de gemiddelde lengte

select Genre.Name, count(track.trackid), avg(track.Milliseconds)
from Genre, Track
where Track.Genreid = Genre.genreid
group by Genre.Genreid;

Vraag 5.1

Maak een lijst van alle artisten met hun albums

Vraag 5.2

Maak een overzicht van de artisten met het aantal albums

Vraag 5.3

Maak een lijst van de artisten met hun tracks

Vraag 5.4

Maak een overzicht van de artisten en hun aantal tracks

Vraag 5.5

Geef de top 3 artisten die de meeste tracks in onze database heeft

Vraag 5.6

Maak een overzicht van alle tracks van de genre 'Opera;

Vraag 5.6

Maak een overzicht per genre van het aantal track en de gemiddelde lengte

De taal SQL kent een aantal functies die je kunt gebruiken om berekeningen uit te laten voeren, of om het aantal rijen te tellen, of om de kolommen op een bepaalde manier af te drukken.

Als je de volgende query gebruikt dan wordt de gemiddelde lengte van een track weergegeven:

(AVG is de afkorting van average, en dat betekent: gemiddelde)

select AVG(Milliseconds) 
from Track;

Hieronder een lijst van de meest belangrijke statistische function binnen SQL(lite).

Een voorbeeld om tellen hoeveel klanten wij hebben:

select count(*) 
from customer;

Round

Nog een voorbeeld van een functie in SQL is de ROUND-functie. ROUND(kolom, aantal decimalen) zorgt ervoor dat de getallen in de betreffende kolom worden afgerond op het aantal decimalen dat je hebt opgegeven.

select Name, Round(avg(Milliseconds), 2) 
from Track;

Kun je uitleggen wat deze functie nu laat zien?

Length

Een andere functie is de functie LENGTH. LENGTH(kolomnaam) : geeft het aantal tekens van de woorden in de kolom.

select Name, Length(Name)
from Track;

Vraag 6.1

Wat is de naam van de track die het langste duurt?

select name 
from Track 
where Milliseconds = (
    select max(Milliseconds) 
    from Track);

Vraag 6.2

Wat is de naam van de track die het kortste duurt?

select name 
from Track 
where Milliseconds = (
    select min(Milliseconds) 
    from Track);

Vraag 6.3

Welk album zijn nog nooit verkocht?

select name 
from track
where trackid not in (
    select t.trackid
    from Track as T, InvoiceLine as I
    where t.trackid = I.trackid);

Vraag 6.4

Wat is de duurste bestelling ooit geweest?

select invoiceID, sum(UnitPrice * Quantity) 
from InvoiceLine 
group by InvoiceID 
order by sum(UnitPrice  * Quantity) 
desc Limit 1;

Vraag 6.5

Welke klant heeft dat besteld?

select DISTINCT Customer.firstname, customer.lastname
from customer, Invoice, Invoiceline
where Customer.CustomerID = Invoice.CustomerID AND
    Invoice.InvoiceID = InvoiceLine.InvoiceID AND
    Invoice.InvoiceID = (
        select invoiceID
        from InvoiceLine 
        group by InvoiceID 
        order by sum(UnitPrice  * Quantity) 
        desc Limit 1);

Vraag 6.1

Wat is de naam van de track die het langste duurt?

Vraag 6.2

Wat is de naam van de track die het kortste duurt?

Vraag 6.3

Welk album zijn nog nooit verkocht?

Vraag 6.4

Wat is de duurste bestelling ooit geweest?

Vraag 6.5

Welke klant heeft dat besteld?

Zoals in paragraaf 1.2 is uitgelegd, kun je met SQL niet alleen gegevens zoeken en opvragen, maar je kunt ook tabellen creëren, gegevens toevoegen, databases aanmaken en nog veel meer. In deze paragraaf leer je hoe je tabellen kunt aanmaken.

Je kan een query uitvoeren op meer dan 1 tabel. Tabellen zijn namelijk gekoppeld. Gegevens uit meerdere tabellen raadplegen in een SELECT opdracht. Alle tabellen die bij 1 raadpleging betrokken zijn, moeten worden opgesomd na het woord FROM.

Om problemen te vermijden moet je aangeven op welke wijze de gegevens uit de tabellen bestelling en bestelregel samengevoegd moeten worden. Dit moet gebeuren in de WHERE-clause. We spreken van een innerjoin, kort weg Join.

Een query kan er zo uitzien:

Dit geeft een overzicht van alle album naam met de artist namen

Graag onderstaande vragen oplossen met functie en niet met 'order by' en Limit

Opdracht 3.1

Hoeveel tracks hebben wij in de onze database?

Opdracht 3.2

Wat is de langste track in de database.

Opdracht 3.3

Wat is de kortste track in de database?:

opdracht 3.4

Hoeveel tracks hebben we met het GerneID nr 10

opdracht 3.5

Hoeveel tracks zijn langer dan de gemiddelde lengte?

Hieronder zie je een strokendiagram. Het gaat om een database van een informatiesysteem waarbij mensen films kunnen beoordelen. Je hebt de volgende tabellen:

De tabel personen: Deze tabel bestaat uit drie velden: persoonsid, voornaam en achternaam De tabel films: Deze tabel bestaat ook uit drie velden: filmid, titel en jaar (jaar waarin de film is uitgekomen). En de tabel rating: Elke rating heeft een unieke id (ratingsid). Persoonsid en filmid houd je natuurlijk ook bij in deze tabel. En natuurlijk de beoordeling (aantal sterren).

Maak nu drie CREATE table instructies waarmee je deze tabellen kunt maken. Maak een nieuwe database in repl.it, je kunt een nieuwe sqllite-repl (vergeet deze niet op te slaan) maken en voer daar CREATE table instructies uit op deze database.

Met het commando CREATE kun je een tabel aanmaken. De basis syntax is:

Hieronder een voorbeeld van bijvoorbeeld een leerling tabel toe te voegen in een database.

Je kunt o.a. kiezen uit de volgende gegevenstypen:

• TEXT: Tekst zonder een vastgestelde lengte

• VARCHAR: Tekst met een bepaalde maximale lengte. Bijv. VARCHAR(15)

• SMALLINT: kleine getallen.

• INT: grote getallen.

• DECIMAL: kommagetallen; je moet het maximale aantal cijfers voor en achter de komma aangeven, bijv. DECIMAL (8,2)

• FLOAT & REAL: dit zijn ook kommagetallen.

• DATE: datum

Verder kun je nog aangeven dat een kolom niet-leeg mag zijn: NOT NULL, wat de primaire sleutel is: PRIMARY KEY.En als de kolom numeriek is kun je aangeven dat het AUTO_INCREMENT is, dan wordt er bij elke nieuwe rij automatisch het eerstvolgende getal genomen.

We Hebben in het vorige hoofdstuk al even een kleine film database gemaakt. We gaan nu een paar bewerkingen uitvoeren op deze database.

Opdracht 8.1

Zet je eigen favoriete film in de database, zet je zelf in de database en geef deze film 5 sterren in de database.

Check je toevoeging en controleer of de id's ook overal kloppen!

Opdracht 8.2

Je hebt toch je rating verkeerd ingeschat, pas je rating aan naar 4 sterren.

Opdracht 8.3

Achteraf besluit je toch maar om al jou eigen ratings te verwijderen. Dus verwijder deze maar.

Hoe zorg je voor dat al je rating wordt verwijderd uit de database?

Aan een database heb je natuurlijk niks als er geen gegevens in staan. In dit hoofdstuk de commando's op gegevens toe te voegen (insert), aan te passen (update) of te verwijderen (delete),

Het wijzigen van gegevens gebeurt met het commando UPDATE. De basis syntax is:

Stel dat je de tabel leerlingen wilt aanpassen. Je wilt de achternaam van de leerling met leerlingnummer 120 veranderen naar Arends, dan kan dat ook met SQL: Deze wijziging wordt ingevoerd m.b.v. de query:

Als je de laatste regel weglaat (dus WHERE llnr = 120 ) dan worden de achternamen van alle leerlingen gewijzigd in Arends. En dat is natuurlijk niet de bedoeling.

Let er daarom op dat je zoveel mogelijk de unieke waarde (of primairy key), in dit geval dus het llnr, neemt als voorwaarde voor een enkele rij met UPDATE.

Als je meerdere kolommen tegelijkertijd wilt wijzigen, dan doe je dat zo:

Stel je dat je de boetes van de leerling met llnr 53 wilt ophogen met 2 euro:

Het verwijderen van rijen uit een tabel gebeurt met het commando DELETE. De basis syntax is:

Dit commando verwijdert hele rijen tegelijk, het is dus heel belangrijk om duidelijk aan te geven aan welke voorwaarden de te verwijderen rijen moeten voldoen om niet de verkeerde gegevens te verwijderen. Als je de voorwaarden weg laat dan worden alle rijen uit de tabel verwijderd! Stel dat lidnr 14 haar lidmaatschap van de tennisvereniging heeft opgezegd, dan kan ze uit de tabel worden verwijderd.

De query wordt dan:

Net zoals bij UPDATE maken we bij de voorwaarden bij DELETE zoveel mogelijk gebruik van de unieke waarde (of primairy key), hier dus het lidnr.

Zouden we de volgende query gebruiken:

dan zouden alle rijen met de naam 'Jan' worden verwijderd.

Met INSERT voegen we gegevens toe aan een tabel. De basis syntax is:

Als je een nieuw lid wilt toevoegen aan de tabel leerlingen, dan kan dat met de volgende query:

Als je deze uit laat voeren wordt er een rij aan de tabel leden toegevoegd.

Een andere manier is dit:

Je ziet dat nu de kolomnamen zijn weggelaten en alleen de waarden worden beschreven. Dan moet wel aan elke kolom een waarde worden gegeven, en in de goede volgorde!

Bij de eerste manier hoef je niet elke kolom een waarde te geven.

Bij SQLLite bestaat het commando auto_increment, dat betekent dat die kolom automatisch met één wordt opgehoogd.

Als de kolom llnr van de tabel leerlingen in SQLLite auto_increment is, dan heeft het geen zin die kolom zelf een waarde te geven.

Met het commando DROP kun je een tabel verwijderen. De basis syntax is:

De query om de leerling-tabel te verwijderen is als volgt:

Wees hier wel voorzichtig mee. Een verwijderde tabel kun je niet ongedaan maken. Je kunt natuurlijk wel het bibliotheek script opnieuw uitvoeren zodat alles weer wordt teruggezet.

Toevoegen van een foreign key

Als je een foreign key wilt toevoegen dan gebruik je de volgende syntax:

Met de volgende opdracht maak je een foreign key aan tussen de tabel boeken en de tabel auteurs.

Dit betekent dus dat er een foreign key verwijzing wordt aangemaakt van de tabel boeken naar de tabel auteurs. Als je dus een boek wilt toevoegen van een auteur, dan moet de desbetreffende auteur wel bestaan in de tabel auteurs!

Verwijderen van een foreign key

Een overzicht van de tabellen, met de kolommen, sleutels en verwijzingen, wordt vaak weergegeven in een strokendiagram.

Dat is een schema waarin de tabellen als stroken verschijnen, met daarin de namen van de kolommen. Hieronder een gedeelte van een tennisvereniging database:

Het sleutelveld wordt aangegeven met een dubbele pijl, die onder de betreffende kolom(men) staat.

En de verwijzingen worden ook met pijlen aangegeven.

Verder zie je onder sommige veldnamen NL staan. Dat is de afkorting van Niet Leeg. In het Engels heet dat NOT NULL

Dat betekent dat die kolom beslist moet worden ingevuld. Als je een nieuwe rij invoert en je vult in die kolom niets in, dan zal het programma een foutmelding geven Tenminste als in het programma is aangegeven dat het een niet-leeg-kolom is.

We Hebben in het vorige hoofdstuk al even een kleine film database gemaakt. We gaan nu een paar bewerkingen uitvoeren op deze database.

Opdracht 8.1

Zet je eigen favoriete film in de database, zet je zelf in de database en geef deze film 5 sterren in de database.

Check je toevoeging en controleer of de id's ook overal kloppen!

Opdracht 8.2

Je hebt toch je rating verkeerd ingeschat, pas je rating aan naar 4 sterren.

Opdracht 8.3

Achteraf besluit je toch maar om al jou eigen ratings te verwijderen. Dus verwijder deze maar.

Hoe zorg je voor dat al je rating wordt verwijderd uit de database?

Door op persoonid te verwijderen en niet op ratingid

De bedoeling van de eindopdracht is dat je zelf een database gaat ontwerpen en gaat maken op repl.it.

Je bent vrij om een model uit te gaan zoeken, overleg evt met je docent. Een paar opties:

• Magister

• Instagram

• Een specifieke game

• leden systeem van je sport vereniging

• Google?

Beoordelingsmodel

In onderstaand model kun je kijken hoeveel punten per onderdeel je kunt krijgen. Hoe uitgebreider het onderdeel hoe hoger het cijfer!

Elke tabel in een database heeft een kolom, of een combinatie van kolommen, die uniek is. Daarmee wordt bedoeld dat elk woord of getal in die kolom (of de combinatie van de woorden en/of getallen in die kolommen) maar één keer voorkomt.

Die kolom, of combinatie van kolommen, noemen we de (primaire) sleutel van de tabel (in het engels primary key).

Voor een primaire steutel geldt:

• In elke rij moet in de sleutelkolom(men) iets zijn ingevuld.

• Deze waarden (of combinaties van waarden) zijn verschillend voor alle rijen.

Bij de tabellen van de database bibliotheek, die in het vorige hoofdstuk beschreven is, heb je ook primaire sleutels.

Bij de tabel leerlingen is het sleutelveld de kolom llnr. Als je het llnr weet dan weet je precies welke leerling er bedoeld wordt.

Als je de kolom naam als sleutelveld kiest dan heb je een probleem als er twee leerlingen met dezelfde naam zijn.

Wel zou je het mobiele nummer als sleutel kunnen kiezen, want twee leden met hetzelfde mobiele nummer zullen niet voorkomen. Als er verschillende mogelijkheden zijn om een sleutel te kiezen dan noem je dat kandidaat-sleutels. Meestal kies je als primaire sleutel de eenvoudigste kandidaat-sleutel; het liefst één kolom, en vaak wordt er daarom een extra kolom toegevoegd met een nummer, die dan als sleutelveld dienst doet.

De tabellen van de database bibliotheek staan niet los van elkaar, er bestaat een bepaald verband tussen de meeste tabellen; ze hebben een relatie.

In de tabel uitleningen staan bijvoorbeeld leerlingennummers (llnr). Als je wilt weten wie bij een gegeven llnr hoort, dan moet je dat opzoeken in de leerlingen tabel.

Zo'verband tussen de gegevens in de ene tabel en die in een andere tabel noem je een verwijzing of referentie-sleutel, in het engels foreign key.

Verwijzingen kunnen door een RDBMS (database programma) worden gecontroleerd. Als je bij een uitlening als boeknummer een nummer invult, dat niet voorkomt bij de boeken-tabel, dan kan het programma een foutmelding geven. Tenminste als in het programma is aangegeven dat die relatie er is.

Het controleren van de verwijzingen noemen we het bewaken van de referentiële integriteit.