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

• Informatica
• WO
• Studiejaar 4,5

SQL

Opdracht: Join

Ga naar https://informatica.stmichaelcollege.nl/ en kies dan SQL

1. gebruik SELECT * FROM ... om de kolommen te vinden van ..._auteurs en ..._boeken
   
   
2. maak gebruik van JOIN ... ON om het
   geboortejaar en achternaam van de auteur
   met de titel van elk boek in hetzelfde
   resultaat te laten zien:

SELECT 

jklos_auteurs.geb_jaar, jklos_auteurs.achternaam, jklos_boeken.titel 

FROM 

jklos_auteurs 

JOIN 

jklos_boeken 

ON 

jklos_auteurs.auteurnr = jklos_boeken.auteurnr;

Antwoord

Een correct statement is (in mijn geval):

SELECT

jklos_auteurs.geb_jaar, jklos_auteurs.achternaam, jklos_boeken.titel

FROM jklos_auteurs

JOIN jklos_boeken

ON jklos_auteurs.auteurnr = jklos_boeken.auteurnr;

>> wat gebeurt er als je de ON regel weglaat?

Als je "ON" en alles erna weglaat, dan krijg je wel een heleboel regels, maar dan klopt het geboortejaar niet meer met de achternaam. De regels matchen dus niet meer!

Stof

SQL

InformaticaBaas 

D. PHP en SQL - SMC

Hoofdstuk 4.1

Leerdoelen

Na deze les kun je:

• comments aan een SQL statement toevoegen
• aangeven wat DISTINCT doet in SQL
• ORDER BY gebruiken in een query
• beschrijven wat ORDER BY en GROUP BY doen
• HAVING gebruiken in een statement
• 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.

Comments

Ook SQL heeft 2 manieren om comments toe te voegen

• Voor een enkele regel gebruikt SQL -- ( spatie achter -- )
• Een blok begint met /* en eindigt met */

Functies in SQL 1/2

Functies in SQL zijn te herkennen aan de () erachter

Functies in SQL accepteren argumenten

Die zijn soms optioneel

Zie dit voorbeeld van een statement met 3 functies:

Functies in SQL 2/2

Er zijn standaardfuncties in SQL:

• COUNT(kolom) -- telt alleen niet-NULL waarden
• COUNT(*)
• SUM(kolom)
• AVG(kolom)
• MIN(kolom) en MAX(kolom)

DISTINCT 1/3

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

Met het SQL-keyword DISTINCT kun je de resultaten van SELECT uniek maken

Let op: als je meerdere velden opgeeft in SELECT, dan wordt bekeken of die combinatie (dus de rij in de output) uniek is

DISTINCT 2/3

Vergelijk zonder DISTINCT:            en met DISTINCT:

DISTINCT 2/3

Vergelijk zonder DISTINCT:            en met DISTINCT:

Waarom zijn ze identiek ook al staat er DISTINCT?

DISTINCT 3/3

Omdat hij kijkt naar de hele rij van de SELECT

De hele rij van de output moet dus uniek (distinct) zijn

Zo zie je het beter (het verschil zit in de SELECT):

ORDER BY

We kunnen de resultaten sorteren via ORDER BY

• ASC (oplopend = ascending)
  dit is de default; als je dus
  niets aangeeft, dan is het ASC
  
  
• DESC (aflopend = descending)

GROUP BY

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

Dit kan gevolgen hebben voor functies (maar hoeft niet)

Zonder GROUP BY telt COUNT() alles bij elkaar tot één getal Met GROUP BY krijg je één getal per groep

HAVING: de where van GROUP BY

Met WHERE gaven we voorwaarden aan de resultaten

Met HAVING geven we voorwaarden aan de GROUP BY

Let op: een combinatie is ook mogelijk: 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 ...

"Kerkstraat" in plaats van 'Kerkstraat'

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

1. Ga naar https://informatica.stmichaelcollege.nl/ en druk op 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 (moet 144 zijn)
   
4. maak een nieuwe query die de uitgeleende titels toont die zijn uitgeleend aan alle leerlingen die in Zaandam wonen (subquery!)
   gebruik geen JOIN ... ON
5. sorteer de selectie van 4 alfabetisch op voornaam

select * from jklos_uitleningen where llnr in (select llnr from jklos_leerlingen WHERE plaats = "Vianen");

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.

limit 1 geeft 1 rij terug... maar dat is willekeurig, dus ORDER BY kan helpen

select * 

from jklos_uitleningen 

where llnr in 

(select llnr from jklos_leerlingen WHERE plaats = "Vianen")

ORDER BY voornaam ASC;

Leerdoelen

Nu kun je:

• comments aan een SQL statement toevoegen
• aangeven wat DISTINCT doet in SQL
  
• ORDER BY gebruiken in een query
  
• beschrijven wat ORDER BY en GROUP BY doen
  
• HAVING gebruiken in een statement
  
• een (eenvoudige) subquery opstellen

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

SQL injection

Exit ticket

https://forms.gle/7J97NiRs9nqWjk5V6