SQL & Databases Les 10

• Informatica
• Middelbare school
• havo, vwo
• Leerjaar 5

Vandaag

• Functies en keywords
• Groeperen
• Voorwaarden aan groepen
• Subqueries

Functies

Niet ieder product heeft een formaat. Bij sommige producten is het formaat null.

De manager twijfelt of de 'Coffee (small)' vaak wordt verkocht. Als dat niet zo is, wil hij het van de kaart halen. Om te weten hoe vaak een 'Coffee (small)' is verkocht, kun je de volgende query gebruiken.

Wat is de prijs van het duurste product?

Wat is de prijs van het duurste product?

Hoeveel calorieën bevat een product uit categorie 4 (salades) gemiddeld?

Hoeveel calorieën bevat een product uit categorie 4 (salades) gemiddeld?

Wat is de gemiddelde prijs van alle verkochte producten op 31 december 2015?

Wat is de gemiddelde prijs van alle verkochte producten op 31 december 2015?

De query die voor de bestelgeschiedenis gebruikt wordt, zou kunnen zijn:

Het keyword DISTINCT

Soms komt het voor dat een waarde meerdere keren voorkomt in dezelfde tabel. Bijvoorbeeld in de tabel product. In categorie 8 staan een aantal drankjes die beschikbaar zijn in verschillende formaten (small, medium, large). Als je een query zou maken om alle beschikbare drankjes op te halen, zal de naam van deze producten dus meerdere keren in het resultaat komen, één keer voor elk beschikbaar formaat.

Dit is niet handig als je snel wilt zien welke drankjes er zijn. Door de dubbele resultaten is het overzicht al snel weg. Gelukkig heeft SQL een functie om dubbele resultaten uit de uitkomst weg te halen, genaamd DISTINCT. Deze functie kan gebruikt worden om snel te zien welke drankjes er zijn.

DISTINCT zorgt er dus voor dat dubbele resultaten worden verwijderd uit het resultaat. Dit gebeurt alleen als de hele regel hetzelfde is. Als je nog een tweede attribuut zou selecteren, bijvoorbeeld de prijs, krijgen je niet meer het gewenste resultaat.
SELECT DISTINCT naam, prijs

Omdat de combinatie van die naam en prijs niet dubbel voorkomt, worden ze niet verwijderd uit de uitkomst. Alle namen zullen dus weer drie keer voorkomen in de uitkomst, met de bijbehorende unieke prijs.

.

Waar het gebruik van meerdere attributen met DISTINCT wel handig is, is bijvoorbeeld om op te zoeken of elk drankje met hetzelfde formaat even groot is. Daarvoor kunnen we het formaat en gewicht selecteren en de dubbelingen eruit halen. Dan valt het in de uitkomst gelijk op dat er een small drankje is met 355ml maar ook een small drankje met 473ml! Ook bij de andere formaten zijn verschillen

Groeperen

Sommige gegevens moeten eerst worden gegroepeerd, voordat je een functie kunt gebruiken. In de app om te bestellen wordt er per categorie weergegeven hoeveel producten er in die categorie zitten. Zoals je weet, kun je met de volgende query producten tellen:

Deze query geeft wel het totaal aantal producten van het restaurant, maar niet per categorie. Om dit resultaat uit te splitsen naar categorie, moet er iets worden toegevoegd.

Geef een overzicht waarin te zien is hoeveel bestellingen er bij ieder klantnummer horen.

Geef een overzicht waarin te zien is hoeveel bestellingen er bij ieder klantnummer horen.

Stel:

Geef een overzicht met daarin de productnummers en hoe vaak dat product is verkocht. Sorteer dit overzicht zo dat producten die het vaakst verkocht zijn, bovenaan staan.

• Je wilt in dit overzicht zien hoe vaak een product is verkocht.
  
• Je moet dus groeperen op product.
  
• De kolom product_id is voor ieder product uniek.
  
• Met de functie SUM kun je alle verkochte aantallen bij elkaar optellen.
• Gebruik deze functie SUM ook in combinatie met ORDER BY voor het sorteren.
  
• Sorteren van laag naar hoog doe je met ORDER BY .. ASC.
•  Sorteren van hoog naar laag doe je met ORDER BY .. DESC

Geef een overzicht met daarin de productnummers en hoe vaak dat product is verkocht. Sorteer dit overzicht zo dat producten die het vaakst verkocht zijn, bovenaan staan.

oplossing

Denice heeft al een aantal keer besteld.

Haar klantnummer is 3768.

Maak voor haar een besteloverzicht waarop de datum te zien is en het bedrag dat ze die dag heeft uitgegeven.

Sorteer het overzicht zo, dat de nieuwste bestelling bovenaan staat.

Denice heeft al een aantal keer besteld. Haar klantnummer is 3768.
Maak voor haar een besteloverzicht waarop de datum te zien is en het bedrag dat ze die dag heeft uitgegeven. Sorteer het overzicht zo, dat de nieuwste bestelling bovenaan staat.

oplossing

Voorwaarden aan groepen

Aan het resultaat van een groepering kun je ook voorwaarden stellen. Stel: je wilt een overzicht van het aantal producten per bestelling. Daarbij wil je alleen bestellingen zien die meer dan 1 product bevatten. Dan stel je een voorwaarde aan een groepering

Een belangrijke regel

Bij WHERE kun je voorwaarden opgeven voor het resultaat. Het is alleen niet mogelijk dat er bij WHERE een voorwaarde wordt gesteld aan het resultaat van een functie, bijvoorbeeld WHERE COUNT(*) > 1.

Voorwaarden waarin een functie wordt gebruikt, mogen alleen bij HAVING staan.

Soms heb je WHERE én HAVING allebei nodig. Bijvoorbeeld als je alleen bestellingen uit 2018 wilt zien, die meer dan 1 product bevatten.

Bestellingen uit 2018 met meer dan 1 product

Geef een overzicht van de klantnamen die meerdere keren voorkomen. Toon ook het aantal keer dat deze klantnaam voorkomt.

• Het is verstandig om de vraag eerst zonder deze voorwaarde (HAVING) te maken. Zorg dus eerst dat je overzicht klopt, daarna pas voeg je de HAVING-voorwaarde toe.
  
• HAVING is er altijd in combinatie met GROUP BY.
• In deze vraag gaat het om klantnamen. Daar moet je dus op groeperen.
• De voorwaarde is dat de namen meerdere keren voor moeten komen.
  
• Bij HAVING moet je dus rijen tellen met COUNT.
  
• De uitkomst hiervan moet groter zijn dan 1

Geef een overzicht van de klantnamen die meerdere keren voorkomen. Toon ook het aantal keer dat deze klantnaam voorkomt.

Subquery's

Met query's vraag je een deel van een databasetabel op. Het resultaat van een query is ook een tabel. Deze tabel kun je vervolgens gebruiken om met een subquery andere gegevens op te vragen.

voorbeeld 1

De manager wil weten van welk product er het meeste aantal zijn verkocht bij één bestelling. Hiervoor moet hij eerst weten wat het grootste aantal verkochte producten is. Die query is eenvoudig.

Het resultaat van deze query kan gebruikt worden om een nieuwe query te maken

Hier staan twee query's in één. Als eerste wordt de onderste query uitgevoerd. De bovenste query gebruikt het resultaat van de onderste query. Het product_id van het product waar er het meeste aantal van zijn verkocht bij één bestelling wordt nu getoond.

Voorbeeld 2

De manager is benieuwd of er producten zijn die nog nooit zijn besteld.

Eerst worden alle product_id's uit de tabel bestelregel geselecteerd. Vervolgens wordt er gekeken naar de tabel product, waarbij het id niet voorkomt in het resultaat van de onderste query. Op deze manier worden de namen van de producten weergegeven die niet voorkomen in de tabel bestelregel.

Het is mogelijk om meerdere subquery's te gebruiken. Belangrijk hierbij is dat het uitvoeren van zo'n query van onder naar boven gaat. Eerst wordt de onderste query uitgevoerd, daarna de query die daarboven staat, enzovoorts.

Welk product is het duurst? Het kan ook zo zijn dat er meerdere producten zijn met die prijs. Geef de namen en prijs van deze producten.

• Bij vragen met een subquery kun je het beste de vraag in twee stukken knippen.
  
• Eerst de subquery maken, en daarna de hoofdquery.
  
• Bij deze vraag ga je dus eerst op zoek naar de hoogste prijs van alle producten. Hiervoor gebruik je de functie MAX. Dit is je subquery.
  
• Vervolgens maak je de hoofdquery, waarbij je de naam en de prijs uit de tabel producten selecteert.
  
• WHERE-voorwaarde hierbij is dat de prijs gelijk moet zijn aan het resultaat van de subquery

oplossing

Wat is de naam van de klant die van een product het grootste aantal stuks heeft besteld tijdens één bestelling?

• Voor deze vraag heb je de gegevens nodig uit drie tabellen, namelijk klant, bestelregel en bestelling.
  
• Als subquery selecteer je het grootste aantal dat ooit van een product besteld is. Hiervoor gebruik je de functie MAX.
  
• In je hoofdquery komen ook de INNER JOINS, je WHERE-voorwaarde
  
• hierbij is dat het aantal bestelde producten gelijk is aan het resultaat van je subquery

Wat is de naam van de klant die van een product het grootste aantal stuks heeft besteld tijdens één bestelling?

oplossing

Volgende les

• Vragenuurtje