SQL 4 - Functies, Distinct, Order by, Group by, Having, Subquery's

• Informatica
• WO
• Studiejaar 4,5

SQL

Fundament

SQL

Fundament kerndomein C: Databases en SQL

Hoofdstuk 3.3 tmt 3.6

https://fundament-online.nl/leeromgeving/content.php?paragraaf_id=114883

Leerdoelen

Na deze les kun je:

• 3 verschillende functies in SQL benoemen
• aangeven wat DISTINCT doet in SQL
• ORDER BY gebruiken in een query
• beschrijven wat ORDER BY en GROUP BY doen
• HAVING gebruiken in een query
• een (eenvoudige) subquery opstellen

Rode achtergrond betekent: klas is stil, dit zijn de leerdoelen en quizzes waarbij niet overlegd mag worden.

Doel van deze les is om het leren zichtbaar te maken voor zowel leerling als docent.

Functies in SQL 1/2

Functies in SQL zijn te herkennen aan () erachter

Functies in SQL accepteren dus argumenten

Zie dit voorbeeld van een query met 3 functies:

Functies in SQL 2/2

Functies in SQL worden meestal gebruikt om te filteren:

• COUNT(kolom)
• COUNT(*)
• SUM(kolom)
• AVG(kolom)
• MIN(kolom)
• MAX(kolom)

DISTINCT

Dubbele waarden kunnen voorkomen in tabellen (ook als de database efficiënt is, bijv. na een JOIN ... ON)

Met DISTINCT kun je de resultaten van SELECT uniek maken

Let op: als je meerdere velden opgeeft, dan wordt bekeken of die combinatie uniek is

ORDER BY

We kunnen de resultaten sorteren via ORDER BY

• ASC (oplopend = ascending)
• DESC (aflopend = descending)

GROUP BY

Met GROUP BY kunnen de resultaten worden gegroepeerd op kolom/veld

Dit kan gevolgen hebben voor functies

Als je bijvoorbeeld een COUNT() functie uitvoert, dan kun je met GROUP BY het aantal voor die groep tellen, terwijl er anders een telling per regel zou worden uitgevoerd

HAVING: de where van GROUP BY

Met WHERE gaven we voorwaarden aan de resultaten

Met HAVING kunnen we voorwaarden stellen aan de GROUP BY

Let op: een combinatie is ook mogelijk, want WHERE en HAVING sluiten elkaar niet uit

Subquery's 1/4

Met subquery wordt bedoeld om een query resultaat in een andere query te gebruiken

Rechts staat een voorbeeld

Laten we die subquery ontleden

Subquery's 2/4

SELECT voornaam, achternaam

FROM leerlingen WHERE leerlingID

= ( SELECT leerlingID FROM adressen

WHERE straatnaam = "Kerkstraat" )

Het belangrijke hier zijn de ( )

Subquery's 3/4

Het andere belangrijke is dat de WHERE van de eerste query het resultaat van de tweede 

query bevat (waarom?)

... WHERE leerlingID = 

( SELECT leerlingID FROM ...

Subquery's 4/4

Natuurlijk kunnen nog meer subquery's worden toegevoegd... een belangrijke vraag

is nu:

In welke volgorde worden de query's 

uitgevoerd?

Subquery's 4/4

Natuurlijk kunnen nog meer subquery's worden toegevoegd... een belangrijke vraag

is nu:

In welke volgorde worden de query's 

uitgevoerd?

Van onder naar boven! Waarom?

Opdracht: Join

1. Ga naar https://informatica.stmichaelcollege.nl
2. Klik op de SQL link
3. Maak een JOIN ... ON statement zodat dit het resultaat is: 
   
   hint:
   gebruik SELECT * FROM ... om de kolommen
   te vinden van leerlingen en adressen
   voornaam en achternaam uit leerlingen
   straatnaam en huisnummer uit adressen

SELECT leerlingen.voornaam, leerlingen.achternaam, adressen.straatnaam, adressen.huisnummer

FROM adressen

JOIN leerlingen ON adressen.leerlingID= leerlingen.leerlingID;

ERD 3/3

Er zijn verschillende notaties in een ERD

Hiermee wordt de relatie aangegeven tussen sleutels

Opdracht

1. Ga naar https://informatica.stmichaelcollege.nl/SQL
2. maak een query die alleen achternamen beginnend met D laat zien in de tabel leerlingen en limit deze query tot 1
   
3. maak een nieuwe query die het aantal leerlingen in de tabel leerlingen geeft
   
4. maak een subquery die de voornamen geeft van leerlingen die wonen in een straatnaam beginnend met een "B"
5. sorteer de uitkomst van 4 alfabetisch op voornaam
6. maak een som van alle huisnummers in adressen

SELECT voornaam, achternaam

FROM leerlingen

WHERE leerlingID = (SELECT leerlingID FROM adressen WHERE straatnaam LIKE "B%");

geeft een foutmelding als er meer dan 1 rij is! Dit komt doordat het = maar 1 waarde verwacht. Vervang = met IN en dan werkt het wel.

SELECT voornaam, achternaam

FROM leerlingen

WHERE leerlingID IN (SELECT leerlingID FROM adressen WHERE straatnaam LIKE "B%")

ORDER BY voornaam ASC;

Hoe zou je zoeken naar alle namen die beginnen met de letter 'A'?

WHERE name LIKE ...

1. '%A%'
2. '_A'
3. 'A%'
4. '%A'

Welke van de volgende zoekopdrachten geeft alle resultaten die eindigen met 'son'?

WHERE name LIKE ...

1. '%son'
2. '_son'
3. 'son%'
4. '%son%'

Leerdoelen

Nu kun je:

• 3 verschillende functies in SQL benoemen
• aangeven wat DISTINCT doet in SQL
• ORDER BY gebruiken in een query
• beschrijven wat ORDER BY en GROUP BY doen
• HAVING gebruiken in een query
• een (eenvoudige) subquery opstellen

COUNT(), MAX(), SUM(), MONTH(), YEAR(), AVG(), MIN()

Sponsopdracht

Klaar om wat meer te spelen met SQL?

Doe het SQL Murder Mystery spel!

https://mystery.knightlab.com/

Je begint met het vinden van de tabel kolommen/velden en gaat dan de informatie met INSERT toevoegen: murder - Jan.15,2018 - SQL City

SQL injection

Exit ticket

https://forms.gle/7J97NiRs9nqWjk5V6