Databases - 1

• Informatica
• Middelbare school
• havo
• Leerjaar 5

CREATE TABLE movies (id INTEGER PRIMARY KEY, name TEXT, release_year INTEGER);

INSERT INTO movies VALUES (1, "Avatar", 2009);

INSERT INTO movies VALUES (2, "Titanic", 1997);

INSERT INTO movies VALUES (3, "Star Wars: Episode IV - A New Hope", 1977);

INSERT INTO movies VALUES (4, "Shrek 2", 2004);

INSERT INTO movies VALUES (5, "The Lion King", 1994);

INSERT INTO movies VALUES (6, "Disney's Up", 2009);

Hoe kun je hier alle gegevens krijgen van de films, die na 1999 uitgekomen zijn?

Groceries

id

name

quantity

aisle

CREATE TABLE groceries (id INTEGER PRIMARY KEY, name TEXT, quantity INTEGER, aisle INTEGER);

Het commando:

Heeft als resultaat: Een tabel met 4 kolommen wordt gemaakt

Met dit commando geef je de structuur aan van de tabel

En je bepaalt wat voor soort gegevens opgeslagen kunnen worden

name

quantity

id

TEXT

INTEGER

INTEGER PRIMARY KEY

Een datacenter? Nee, miljoenen gesorteerde blokjes Lego

Als data, net als Lego, gesorteerd is, gestructureerd, kun je er veel efficiënter in zoeken, data aan toevoegen, veranderen en verwijderen

Met SQL maak je een tabel om data op een gestructureerde manier op te slaan.

Hierdoor kun je data makkelijk toevoegen, veranderen, verwijderen

Wat betekent SQL en wat is het?

CREATE TABLE groceries (Id INTEGER PRIMARY KEY, name TEXT, quantity INTEGER);

INSERT INTO groceries VALUES (1, "Alles wat er was", 4);

INSERT INTO groceries VALUES (2, "Efter", 1);

INSERT INTO groceries VALUES (3, "Anne Frank", 2);

SELECT * FROM groceries;

Dit is op zich goed, maar wat zou handiger zijn, als je naar de naam van de tabel kijkt?

Naam: Boeken

Querying the table: Video 2

Met het commando SELECT * FROM...... krijg je alle rijen te zien.

Het commando SELECT wordt uitgebreid met ORDER BY aisle. De rijen worden getoond met de gangpaden oplopend.

En dan wordt een WHERE toegevoegd. De maakster wil alleen de gangpaden boven de 5, haar broer doet de rest.

Challenge Box office hits database

Je krijgt een tabel met films en het jaar van uitkomen, release_year.

Opdracht 1: Geef alle films

Opdracht 2: geef alle films die na 2000 zijn uitgekomen EN laat de lijst sorteren, zodat de films die het eerste uitkwamen ook het eerste getoond worden.

Groceries

id	name	quantity	aisle
1	Bananas	4	7
2	Peanut Butter	1	2
3	Dark choc bars	2	2

INSERT INTO groceries VALUES (1, "Bananas", 4, 7 );

INSERT INTO groceries VALUES(2, "Peanut Butter", 1, 2);

INSERT INTO groceries VALUES(3, "Dark Chocolate Bars", 2, 2);

CREATE TABLE groceries (id  

CREATE TABLE Boekenlijst (id INTEGER, name TEXT, rating INTEGER);

Wat gebeurt er in het volgende statement?

Wat ontbreekt in het volgende SQL-statement?
CREATE TABLE Boekenlijst (id INTEGER PRIMARY KEY, name TEXT, rating INTEGER

Er wordt een tabel gemaakt, Boekenlijst, met de kolommen: id, name, rating. Ook worden de datatypes bepaald, dus welke soort gegevens opgeslagen kunnen worden

Wat wordt niet geaccepteerd in het volgende SQL-statement in de Khan-cursus?
CREATE TABLE Boekenlijst (id INTEGER, name String, rating INTEGER);

Datatype String

INSERT INTO Boekenlijst VALUES (1, "Game of Thrones", 9);

Wat gebeurt er in het volgende statement?

er wordt een rij aan de tabel toegevoegd

Wat gaat hier fout?

INSERT INTO Boekenlijst VALUES (1, "Game of Thrones", 9);

CREATE TABLE Boekenlijst (id INTEGER, name TEXT, rating INTEGER);

Dit moet andersom gebeuren

De volgende regels bevatten 3 fouten. Welke?
CREATE TABLE Boekenlijst (id INTEGER, name TEXT, rating INTEGER
INSERT INTO boekentlijst VALUES (1, Game of Thrones, 9);

Sluithaakje, boekentlijst (tikfout), missende aanhalingstekens

De volgende waarden worden geaccepteerd. Wat is daar niet handig aan?
CREATE TABLE Boekenlijst (id INTEGER, name String, rating INTEGER);
INSERT INTO boekenlijst VALUES (1, "Game of Thrones", 9);
INSERT INTO boekenlijst VALUES (1, "Game of Thrones", 8);
INSERT INTO boekenlijst VALUES (1, "Game of Thrones", 7);

elke rij hetzelfde id

Wat betekent aggregatie van gegevens

1. Sorteren van gegevens
2. Groepering van gegevens
3. Verwijderen van gegevens
4. Veranderen van gegevens

Bekijk video: Aggregating data (3 min)

• Aggregating = groeperen van data
  
• Dus bv alle mensen uit nieuw Vennep
  
• Of mensen boven de 60
  

CREATE TABLE groceries (id INTEGER PRIMARY KEY, name TEXT, quantity INTEGER, aisle INTEGER);

Primary key: Uniek veld

Beantwoord de volgende vragen over dit filmpje: Aggregating

• Na 1 min: Wat betekent het statement: SELECT SUM(quantity) FROM groceries;
  
• Dit statement wordt veranderd in: SELECT MAX(quantity) FROM groceries; Wat betekent dit?
  
• Wat betekent het statement: SELECT aisle, SUM(quantity) FROM groceries GROUP BY aisle;
  
• Waarom heeft het volgende statement niet zoveel zin: SELECT name, SUM(quantity) FROM groceries GROUP BY aisle;
  
• Na 2:17 min: Hoe werkt SQL ‘behind the scenes’:
  
• Probeer te achterhalen in welke volgorde de onderdelen worden uitgevoerd van:
  

         SELECT aisle, SUM(quantity) FROM groceries GROUP BY aisle;

id	name	quantity	aisle
1	bananas	4	7
2	peanut butter	1	2
3	dark choc bars	2	2
4	ice cream	1	12
5	cherries	6	2
6	choc syrup	1	4

SUM(quantity)

Waarom heeft het commando:

SUM(aisle) geen zin? Of SUM(name)

Het tellen van rijen gaat met het commando COUNT

Daarvoor moet je eerst rijen groeperen 

Sorteren doe je met ORDER BY. Dit hoeft niet met een groepering. Bekijk dia 17 nogmaals voor een sortering

Welke van het volgende is een groepering?

1. Het veranderen van een adres
2. Het toevoegen van een klant
3. Personen van boven de 50
4. Het sorteren van lego

Groeperingen in SQL zijn:

MIN, MAX, COUNT, AVG

Dus: Het minimum, het maximum, het aantal rijen, het gemiddelde

Welke groepering (aggregation) kun je hier ontdekken?

1. Het aantal producten per gangpad
2. Het maximum aantal producten
3. Het gemiddelde aantal producten
4. Het aantal producten met chocola

Na elk SQL- statement wordt er een tussentabel gemaakt, die je niet te zien krijgt

Ik ga laten zien in welke volgorde de SQL-statements uitgevoerd worden en welke tussentabellen er gemaakt worden.

Dit is handig om te weten, om een groepering te maken

Als je iets wilt groeperen, is het handig om het woord "per" te gebruiken: 

• Het aantal producten per gangpad
• Het aantal mensen per rol

Waar "per" voor staat, daar komt de GROUP BY

• GROUP BY aisle
• GROUP BY Role

We nemen een tabel uit de cursus: Movies, maar iets aangepast om het vb te verduidelijken

Het volgende statement wordt uitgevoerd:

SELECT COUNT(*), release_year FROM movies GROUP BY release_year

Bepaal de tussentabellen, die ontstaan en bepaal zo wat het resultaat van het statement wordt

Je krijgt hier 4 minuten voor, kijk er samen naar, daarna doen we het gezamenlijk

We gaan samen een voorbeeld uitwerken op deze site

1. Vind het aantal Artiesten. Hiervoor tel je het aantal rijen waar de Role Artist is. Voor zoiets gebruik je COUNT(*)
2. Vind het aantal werknemers PER Role. Waar groepeer je dan op?
3. Geef het totaal aantal Years-employed. Wat gebruik je dan?

Je krijgt op de volgende dia een tabel te zien, iets aangepast, Movies.

Dit is de tabel NA het FROM-statement, dus een kopie van de originele tabel

het volgende statement wordt uitgevoerd:

SELECT COUNT(*), release_year FROM Movies GROUP BY release_year

Na FROM wordt GROUP BY uitgevoerd.

Teken de tabel, die dan wordt gemaakt

id	name	release_year
1	Avatar	2009
2	Titanic	1997
3	Star Wars	1997
4	Shrek 2	2004
5	The Lion King	1994
6	Disney's up	2004

SELECT COUNT(*), release_year FROM Movies GROUP BY release_year

id	name	release_year
1	Avatar	2009
2,3	Titanic, Star Wars	1997
4, 6	Shrek 2, Disney's up	2004
5	The Lion King	1994

Na GROUP BY ontstaat dan deze tabel.

Je kunt dan nog toevoegen: ORDER BY release_year, of HAVING release_year < 2000. Probeer het!

SELECT COUNT(*), release_year FROM movies GROUP BY release_year HAVING release_year >2000 ORDER BY release_year DESC;

Probeer dit statement:

Start de opdracht met aggregaties: Challenge: TODO list database stats

Aggregatie: Min, Max, Sum, Count

Aggregatie is een groepering van rijen, waarna je bepaalt wat je ervan wilt weten

Bv.

• Je wilt weten hoeveel klanten Visser heten: COUNT.
  

1. Met COUNT tel je het aantal rijen, dat aan een bepaalde voorwaarde voldoet
2. Met COUNT(*) tel je dan alle rijen

• Je wilt weten wat je laagste cijfer voor wiskunde is: MIN
  
• Je wilt het hoogste cijfer weten: MAX
  
• Je wilt weten hoeveel producten er in een bepaald gang liggen: SUM
  

Voeg in de Challenge: TODO list database stats de volgende statements toe:

• De taak met het minste aantal minuten
  
• De taak met de hoogste aantal minuten
  
• Laat het totaal berekenen van het aantal minuten
  
• Geef het aantal rijen
  
• Haal een INSERT weg (door er commentaar van te maken) en bekijk hoe dit de uitkomsten beïnvloedt.
  

Lever deze opdracht in