All I ever wanted to do was to make food accessible to everyone; to show that you can make mistakes – I do all the time – but it doesn't matter.
—Jamie Oliver

Oefeningen SQL op databank voor een kookclub

Voorstelling model

In dit laatste hoofdstuk willen we je een idee geven van de moeilijkheidsgraad van de oefeningen op het examen. We recycleren hiervoor een model dat op een vorig examen gebruikt is voor een kookclub.

Je vindt dit model (‘kookclub’) in de databank ‘df’. Je hebt hier enkel SELECT rechten en kan dus geen INSERT, CREATE enz uittesten.

Voor dit examen maken we gebruiken van een eenvoudig model van een organisatie die kookworkshops inricht. Dit is het fysiek model:

We willen graag de organisatie van kookworkshops automatiseren. Een kookworkshop gaat telkens over een bepaald thema waarbinnen dan bepaalde gerechten gemaakt worden. Leden kunnen zich inschrijven om aan zo’n workshop deel te nemen en kunnen na deelname ook een score geven en wat feedback. Beide attributen zijn dus niet verplicht.

Op het moment dat een kookworkshop georganiseerd wordt, wordt zoals gezegd het thema bepaald. Binnen elk thema zijn een aantal gerechten gekend. Bij het vastleggen van de gerechten die in een kookworkshop aan bod zullen komen, kan uit het aanbod binnen dat thema gekozen worden, maar is het ook mogelijk om een nieuw gerecht toe te voegen. Uiteraard wordt dat nieuw gerecht dan ook aan dat thema gekoppeld. Ook wordt bijgehouden voor elk gerecht wat de ingrediënten zijn en in welke hoeveelheid ze voor dat gerecht gebruikt worden.

Let op bij ingrediënt: de eenheid geeft weer of dat ingrediënt per g, per ml, per eetlepel, per stuk of andere gebruikt wordt. Het energieniveau geeft het aantal kcal weer. Let op: als de eenheid uitgedrukt wordt in g of ml, dan is de energie altijd per 100 g, resp ml gegeven. In de andere gevallen is het per eenheid die aangegeven is.

Ga bvb na dat aardbeien per 100 g 32 kcal energie hebben en dat één abrikoos een energie-inhoud van 27 kcal heeft.

SELECT *
FROM kookclub.ingrediënt -- je kan ook je search_path goed zetten …
WHERE naam IN ('Aardbei','Abrikoos')

Vergeet niet je search_path goed te zetten ofwel telkens de naam van het schema voor de naam van een tabel te vermelden …

Tip: bestudeer nu eerst rustig het model vooraleer je naar de vragen kijkt. Bekijk de inhoud van alle tabellen zodat je een idee krijgt waar welke informatie zit en hoe uitgebreid sommige tabellen zijn. Een deel van de databank is automatisch gegenereerd (bvb lid en deelname), maar veel tabellen zijn toch manueel ingevuld zodat de data er redelijk realistisch uitziet.

Hieronder vind je nu heel wat oefeningen zoals die ooit op een examen gevraagd zijn. Er zijn heel wat verschillende types:

Geef het antwoord op een vraag, geen SQL-code gevraagd.
Geef de SQL-code die een vraag beantwoordt.
Pas de databank aan: voeg gegevens toe, verander dingen (kan je niet uittesten, want je hebt daarvoor niet de juiste privileges).
Wat doet volgende SQL-code?
Breid het model uit met een nieuwe functionaliteit.

Vragen waarbij je enkel het antwoord moet geven

Voor deze oefeningen moet je enkel kort een antwoord geven (naam, ingrediënt, gemeente, … ). Je hoeft dus niet de query te geven waarmee je dit antwoord vond. In de oplossingen zal je meestal wel de query vinden zodat je kan controleren wat je fout deed.

Het schema bevat een uitgebreide lijst van ingrediënten. Sommige hiervan zijn per gram, andere per milliliter, per eetlepel, nog andere per stuk enz. Stel dat je van elk ingrediënt dat per stuk geteld wordt er 1 eet, hoeveel energie (kcal) heb je dan binnen?

Antwoord: 7071 kcal. Een mogelijke query hiervoor is:

SELECT sum(energie)
FROM ingrediënt
WHERE eenheid = 'stuk'

Dit schema is voor een deel opgevuld door automatische datageneratie. Dat is niet al te slim gebeurd. Zo kan het natuurlijk niet dat een inschrijfdatum na de kookworkshop zelf valt. Deze fout moet dus manueel rechtgezet worden. Hoeveel van dergelijke foute inschrijvingen zijn er?

Antwoord: 57, mogelijke query:

SELECT COUNT(*)
FROM deelname D INNER JOIN kookworkshop K ON D.workshop = K.workshop_id
WHERE D.inschrijfdatum > K.startmoment

Bekijk alle hoofdgerechten die ooit in een kookworkshop gemaakt zijn. Daarvoor waren heel wat ingrediënten nodig. Als je die ingrediënten schrapt uit de lange lijst van ingrediënten en je ordent de overblijvende lijst alfabetisch, welk ingrediënt dat voor geen enkel hoofdgerecht gebruikt is, staat dan op plaats 81?

Antwoord: Naanbrood, oplossing gevonden bvb via:

SELECT naam, IG.gerecht
FROM gerecht_in_workshop GW
  INNER JOIN ingrediënt_in_gerecht IG on GW.gerecht = IG.gerecht AND rol_in_menu = 'Hoofdgerecht'
  RIGHT OUTER JOIN ingrediënt I ON IG.ingrediënt = I.naam
WHERE IG.gerecht is NULL
ORDER BY 1

Wie is de jongste deelnemer aan een kookworkshop die in de evaluatie het woord ‘geweldig’ of ‘fantastisch’ gebruikt heeft? Geef voornaam gevolgd door naam.

Antwoord: Ramiro Bell, met bvb. volgende query:

SELECT voornaam, naam, geboortedatum, feedback
FROM deelname D INNER JOIN lid L ON D.lid = L.lidnr
WHERE feedback LIKE '%geweldig%' OR feedback LIKE '%fantastisch%'
ORDER BY 3 DESC

Modelleervraag

In deze vraag moet je iets toevoegen aan het model. Je kan dat zelf niet uittesten want je hebt immers enkel SELECT rechten op het schema. Je tekent voor deze vraag iets op het ERD (fysiek datamodel) van het schema en geeft de nodige SQL query’s.

We willen bijhouden hoeveel ingrediënten we voor een bepaalde workshop moeten bestellen. We moeten daarvoor bijhouden hoeveel we van elk ingrediënt op welke datum besteld hebben.

Teken de nodige uitbreiding aan het schema.
Schrijf de wijzigingen die nodig zijn in het CREATE script. Zorg er zeker ook voor dat de bestelde aantallen altijd strikt positief zijn. Vermeld alle integriteitsregels.
Voor kookworkshop 7 moeten volgende ingrediënten besteld worden: 600 g gember (80 kcal per 100 g), 180 g wasabi (241 kcal per 100 g), 6 kg zalm (137 kcal per 100 g) en 7 broccoli’s (35 kcal per stuk). Schrijf de nodige INSERT statements hiervoor.

Je moet een tabel bijmaken:

CREATE  TABLE kookclub.bestelling (
  ingrediëntnaam       varchar(30)  NOT NULL ,
  workshop_id          smallint  NOT NULL ,
  besteldatum          date NOT NULL ,
  aantal               smallint  NOT NULL ,
  CONSTRAINT pk_bestelling PRIMARY KEY ( ingrediëntnaam, workshop_id, besteldatum ),
  CONSTRAINT fk_bestelling_ingrediënt FOREIGN KEY ( ingrediëntnaam )
      REFERENCES kookclub.ingrediënt( naam )   ,
  CONSTRAINT fk_bestelling_kookworkshop FOREIGN KEY ( workshop_id )
       REFERENCES kookclub.kookworkshop( workshop_id )   ,
  CONSTRAINT cns_bestelling CHECK ( aantal > 0 )
);

De extra informatie toevoegen gaat als volgt: bestelling voor 0,6 kg gember (80 kcal per 100 g), 180 g wasabi (241 kcal per 100 g), 6 kg zalm (137 kcal per 100 g) en 7 broccoli's (35 kcal per stuk) die gebruikt zullen worden in kookworkshop 7 omdat we daar vis op een oosterse manier zullen klaarmaken. Gember, Zalm en Broccoli staan al in de tabel ingrediënt. Wasabi staat er nog niet in, dus eerst moet je dat ingrediënt toevoegen:

INSERT INTO ingrediënt VALUES ('Wasabi', 'g', 241);

Nu kunnen we de bestelling in orde maken:

INSERT INTO bestelling VALUES ('Gember', 7, '2021-06-18', 600);
INSERT INTO bestelling VALUES ('Wasabi', 7, '2021-06-18', 180);
INSERT INTO bestelling VALUES ('Zalm', 7, '2021-06-18', 6000);
INSERT INTO bestelling VALUES ('Broccoli', 7, '2021-06-18', 7);

Gegeven de query, wat is de vraag?

Voor deze oefening krijg je een query die het antwoord op een bepaalde vraag is. Wat was de vraag? Geef je antwoord zo volledig mogelijk in vlot Nederlands. Je mag gerust meerdere zinnen gebruiken om die vraag duidelijk te omschrijven.

SELECT distinct gemeente
FROM gemeente LEFT OUTER JOIN lid using(postcode)
  INNER JOIN deelname ON lid.lidnr = deelname.lid
  INNER JOIN gerecht_in_workshop using(workshop)
  INNER JOIN ingrediënt_in_gerecht using(gerecht)
GROUP BY gemeente, deelname.lid
HAVING COUNT(distinct ingrediënt) > 30
ORDER BY 1 ASC

Geef een alfabetisch gerangschikte lijst van alle gemeenten waar inwoners aan een of meerder kookworkshops hebben deelgenomen met in totaal meer dan 30 verschillende ingrediënten (over alle kookworkshops van dit lid heen). Als er meerdere van dergelijke inwoners zijn mag de gemeente maar één keer vermeld worden in de lijst. De OUTER JOIN heeft geen effect en wordt niet vertaald in de opgave.

SQL query's

Het volgende type oefeningen vraagt de volledige query als antwoord te geven.

Geef een overzicht dat per ingrediënt toont in welke thema’s dat ingrediënt gebruikt wordt. Er is een extra kolom die de energie van een ingrediënt in een woord weergeeft: lager dan 100 (kcal) is ‘laag’, tussen 100 en 300 is ‘gemiddeld’ en hoger dan 300 is ‘hoog’. Vermijd herhaling van rijen. Zet de ingrediënten in alfabetische volgorde. Schrijf de query.

SELECT distinct I.naam AS ingrediënt, GT.thema AS "naam thema", 
  CASE
    WHEN energie < 100 THEN 'laag'
    WHEN energie > 300 THEN 'hoog'
    ELSE 'gemiddeld'
  END AS energiegehalte
FROM ingrediënt I
    INNER JOIN ingrediënt_in_gerecht IG ON (I.naam = IG.ingrediënt)
    INNER JOIN gerecht_past_in_thema GT using(gerecht)
ORDER BY 1

Geef per thema een overzicht (zie figuur) per gemeente hoeveel deelnemers uit die gemeente komen. Rangschik alfabetisch per thema en binnen één thema volgens dalend aantal leden per gemeente. Schrijf de query.

SELECT thema, gemeente, COUNT (lidnr) AS "aantal leden per gemeente"
FROM lid
  INNER JOIN gemeente USING (postcode)
  INNER JOIN deelname ON (lid = lidnr)
  INNER JOIN kookworkshop ON (workshop = workshop_id)
GROUP BY thema, postcode, gemeente
ORDER BY thema, COUNT(lidnr) DESC

Een alternatief waarbij gemeente in de GROUP BY weg mag omwille van het niet gebruiken van USING:

SELECT thema, gemeente, COUNT (lidnr) AS "aantal leden per gemeente"
FROM lid
  INNER JOIN gemeente ON gemeente.postcode = lid.postcode
  INNER JOIN deelname ON (lid = lidnr)
  INNER JOIN kookworkshop ON (workshop = workshop_id)
GROUP BY thema, gemeente.postcode
ORDER BY thema, COUNT(lidnr) DESC

Hoeveel kcal zitten er in het gerecht ‘Tiramisu met chocolade en banaan’ per ingrediënt? Opgepast, waar de eenheid ‘g’ of ‘ml’ is, is de energie-inhoud het aantal kcal per 100 g of 100 ml. Voor alle andere eenheden is het getal in kcal gewoon de energie per eenheid (stuk, eetlepel, bussel enz.). Hou dus rekening met de eenheden. Het ingrediënt dat de grootste energiebijdrage levert aan dit gerecht staat bovenaan, dan het tweede enz. Zie figuur. Schrijf de query.

SELECT IG.ingrediënt, hoeveelheid, eenheid,energie,
  CASE
    WHEN eenheid in ('g','ml') THEN energie * hoeveelheid / 100
    ELSE energie * hoeveelheid
  END AS totaal
FROM ingrediënt_in_gerecht IG INNER JOIN ingrediënt I ON IG.ingrediënt = I.naam
WHERE gerecht = 'Tiramisu met chocolade en banaan'
ORDER BY 5 DESC

Vragen waarbij je enkel het antwoord moet geven, deel 2

Geef een alfabetisch gesorteerd overzicht van alle ingrediënten die nog niet in een gerecht gebruikt worden. Welk ingrediënt staat op plaats 100 en wat is de energie-waarde van dit ingrediënt. Je antwoord bevat dus twee delen zoals bvb: “Bloemkool met 25 kcal”.

Antwoord: snijbiet met 19 kcal. Mogelijke query:

SELECT ingrediënt.naam
FROM ingrediënt LEFT OUTER JOIN ingrediënt_in_gerecht ON (ingrediënt.naam = ingrediënt)
WHERE gerecht.naam IS NULL
ORDER BY ingrediënt.naam

Bekijk alle deelnames aan kookworkshops van mensen uit een gemeente die begint met een ‘N’ of eindigt met een ‘n’. Hoeveel deelnemers uit deze gemeenten hebben een score van minstens 7 gegeven?

Antwoord: 10. Mogelijke query:

SELECT score, lidnr, gemeente, postcode
FROM deelname D
    INNER JOIN lid L ON D.lid = L.lidnr
    INNER JOIN gemeente using(postcode)
WHERE (gemeente LIKE 'N%' OR gemeente LIKE '%n') AND score >= 7

Bekijk de lange lijst van ingrediënten, maar beperk je tot die ingrediënten die in gram (’g’) worden afgemeten. Bereken de gemiddelde energie van deze ingrediënten (dat is per 100 g gemeten, maar voor deze vraag is dat niet belangrijk). Welk ingrediënt dat per g afgemeten wordt, heeft een energie-inhoud die het dichtst ligt bij dit gemiddelde?

Antwoord: Advokaat (240 Kcal per 100 g, ligt dichtst bij gemiddelde 226,5). Dit kan je het eenvoudigst bekomen met twee kleine query's, maar het kan natuurlijk mooier met één query (die een subquery bevat).

SELECT naam,energie -- eerst vraag je echter avg(energie)
FROM ingrediënt
WHERE eenheid = 'g'
ORDER BY 2

Geef voornaam en naam van het lid met de meeste deelnames. Als er meerdere leden zijn met hetzelfde maximale aantal deelnames, geef je alle namen (voornaam gevolgd door familienaam).

Antwoord: Benny Nielsenn, Casey Valentine, Shelley Vincent

SELECT naam, voornaam, COUNT(*)
FROM deelname D INNER JOIN lid L ON D.lid = L.lidnr
GROUP BY lidnr
ORDER BY 3 DESC, 1

Gegeven de query, wat is de vraag, deel 2?

SELECT G.naam
FROM thema T
    INNER JOIN gerecht_past_in_thema GT ON T.naam = GT.thema
    RIGHT OUTER JOIN gerecht G ON GT.gerecht = G.naam AND
        T.naam IN ('Mediterraans','Italiaans','vis')
WHERE T.naam is NULL AND G.beschrijving NOT LIKE '%zomer%'
ORDER BY bereidtijd DESC

Dit is een moeilijke oefening, zie ook de titeltje “Rijen selecteren die NIET aan een bepaalde voorwaarde voldoen in het stukje over OUTER JOIN. Geef een lijst van alle gerechten in de databank die niet als thema Mediterraans, Italiaans of vis hebben en waar in de beschrijving van het gerecht het woord zomer niet voorkomt, gesorteerd volgens dalende duur om het gerecht klaar te maken.

SQL query's, deel 2

Schrijf een query die voor alle gerechten die beschikken over een ingrediëntenlijst de totale energie-inhoud in kcal berekent. Opgelet: bij ingrediënten die als eenheid ‘g’ of ‘ml’ hebben is de energie-inhoud per 100 g of 100 ml gegeven. Bij alle andere eenheden is de energie-inhoud gegeven per eenheid (‘stuk’, ‘dl’, ‘koffielepel’, …). We willen dit overzicht graag gebruiken om een lijstje te krijgen van alleen ‘lichte’ gerechten die in totaal minder dan 4000 kcal bevatten. Het gerecht met het minste aantal kcal staat bovenaan, daarna het tweede enz. De screenshot toont alleen de eerste twee rijen.

SELECT IG.gerecht,
  sum(CASE
    WHEN eenheid in ('g','ml') THEN energie * hoeveelheid / 100
    ELSE energie * hoeveelheid
  END) AS "totaal aantal kcal"
FROM ingrediënt_in_gerecht IG INNER JOIN ingrediënt I ON IG.ingrediënt = I.naam
GROUP BY IG.gerecht
HAVING sum(CASE
    WHEN eenheid in ('g','ml') THEN energie * hoeveelheid / 100
    ELSE energie * hoeveelheid
  END) < 4000
ORDER BY 2

Bij de inschrijvingen heeft men niet gekeken naar het maximale aantal deelnemers van elke kookworkshop. Dat is natuurlijk stom, want de mensen die ingeschreven zijn nadat het maximale aantal deelnemers bereikt is, moeten verwittigd worden dat de workshop al volzet is. Schrijf een query die de lijst genereert van alle workshops die overboekt zijn. De meest overboekte workshop staat bovenaan, daaronder die met de tweede grootste overboeking enz. De screenshot in de figuur hieronder toont alleen de eerste drie rijen.

SELECT workshop_id, max_deelnemers, COUNT(*) AS "ingeschreven aantal"
FROM kookworkshop KW INNER JOIN deelname D ON D.workshop = KW.workshop_id
GROUP BY workshop_id
HAVING COUNT(*) > max_deelnemers
ORDER BY (COUNT(*) - max_deelnemers) DESC

Vragen waarbij je enkel het antwoord moet geven, deel 3 (+ video)

Bij deze oefening hoort telkens een video (in het Nederlands) die wat extra uitleg geeft hoe je aan een dergelijke oefening kan beginnen. Die video vind je telkens bij de oplossing van elke oefening.

Maak een lijst van alle leden die nog geen enkele workshop meededen en wonen in een straat die eindigt op ‘pad’. Rangschik deze lijst van oud naar jong. Wat is de voornaam van de persoon die op plaats 7 staat in deze lijst?

Antwoord: Angelica. Mogelijke query:

SELECT *
FROM lid L LEFT OUTER JOIN deelname D ON L.lidnr = D.lid
WHERE straat LIKE '%pad' AND lid is NULL
ORDER BY 4

Maak een lijst per gemeente met de gemiddelde score die mensen uit die gemeente gaven aan een kookworkshop. Sorteer deze lijst zodat de hoogste gemiddelde scores bovenaan staan. Binnen eenzelfde gemiddelde score sorteer je alfabetisch. Gemeenten waar niemand een score gaf worden niet opgenomen in deze lijst. Welke gemeente (naam) staat bovenaan?

Antwoord: Barvaux-Condrox. Mogelijke query:

SELECT L.postcode, avg(score), gemeente
FROM deelname D
  INNER JOIN lid L ON D.lid = L.lidnr
  INNER JOIN gemeente G ON G.postcode = L.postcode
WHERE score IS NOT NULL
GROUP BY L.postcode, gemeente
ORDER BY 2 DESC, 3 ASC

Bereken het percentage deelnames dat een positieve evaluatie meekreeg. Met ‘positief’ bedoelen we dat de evaluatie minstens één van volgende woorden moet bevatten: ‘goed’, ‘geweldig’ of ‘fantastisch’. Tip: je kan twee vrij korte query's maken die elk een getal uitkomen en dan met je rekenmachine zelf het percentage berekenen. Het kan natuurlijk ook in één query.

Antwoord: 28,75%. Dit zijn 115 goede deelnames:

SELECT COUNT(*)
FROM deelname
WHERE feedback LIKE '%goed%' or feedback LIKE '%geweldig%' or feedback LIKE '%fantastisch%'

In totaal zijn er 400 deelnames:

SELECT COUNT(*)
FROM deelname

Maak een lijst van alle ingrediënten die juist twee keer een letter ‘a’ bevatten. We houden geen rekening met hoofdletter / kleine letters, dus een woord zoals ‘Aardbei’ voldoet ook aan de voorwaarde, net zoals ‘Gehaktbal’. Sorteer volgens dalende energie-inhoud. Welk ingrediënt staat op plaats 21?

Antwoord: vegetarische kebab. Mogelijke query:

SELECT *
FROM ingrediënt
WHERE (lower(naam) LIKE '%a%a%') AND NOT(lower(naam) LIKE '%a%a%a%')
ORDER BY 3 DESC

Genereer een overzicht van alle inschrijvingen voor workshops waarvoor de deelnemer een feedback schreef en met een inschrijfdatum voor 1 januari 2019. Deze lijst sorteer je alfabetisch op naam. Geef de voornaam en familienaam van de persoon die op plaats 100 staat.

Antwoord: Sonya Osborne. Query:

SELECT naam, voornaam, lid, inschrijfdatum, feedback
FROM deelname D INNER JOIN lid L ON D.lid = L.lidnr
WHERE feedback IS NOT NULL AND inschrijfdatum < '2019-01-01'
ORDER BY 1

Rangschik alle thema’s naar dalend aantal inschrijvingen, zodat het thema waar het meest mensen op inschreven bovenaan staat. Hoeveel inschrijvingen had het thema dat in deze lijst op de vijfde plaats staat?

Antwoord: 41

SELECT thema, COUNT(*)
FROM deelname D INNER JOIN kookworkshop KW ON workshop = workshop_id
GROUP BY thema
ORDER BY 2 DESC

Databankgegevens aanpassen (+ video)

De gemeenten Overpelt en Neerpelt zijn recent een fusie aangegaan. Beiden samen vormen nu de gemeente ‘Pelt’. Op de site van de gemeente Pelt lees je: “3900 Overpelt wordt 3900 Pelt en 3910 Neerpelt wordt 3910 Pelt”. Jij moet als database-administrator voor de kookclub zorgen dat deze informatie correct in de databank wordt aangepast. Schrijf één query die dit doet. Opgepast: omdat je enkel SELECT rechten hebt op de databank, zal je de query niet kunnen uittesten.

UPDATE kookclub.gemeente
SET gemeente = 'Pelt'
WHERE postcode = '3900' OR postcode = '3910'

Gegeven de query, wat is de vraag, deel 3 (+ video)?

Gegeven volgende query. Omschrijf nauwkeurig en kort (één zin) wat de vraag was waarop de query het antwoord geeft. Begin je antwoord met “Geef een overzicht van alle … ”. Deze oefening is moeilijker dan je op het eerste zicht zou zeggen.

SELECT naam
FROM thema T LEFT OUTER JOIN kookworkshop K ON T.naam = K.thema AND max_deelnemers > 35
WHERE thema IS null

“Geef een overzicht van alle thema's (naam volstaat) die niet aan bod komen in een kookworkshop met een maximumcapaciteit van meer dan 35 deelnemers.” Alternatief: “Geef een overzicht van alle thema's die nog niet in een kookworkshop aan bod gekomen zijn of enkel aan bod kwamen in een kookworkshop met hoogstens 35 deelnemers.”

Informatie toevoegen aan de databank (+ video)

Jeroen Meus maakte in zijn kookprogramma ‘Dagelijkse Kost’ onlangs ‘Kaaskroket met ham en asperges’. Dat zag er bijzonder lekker uit. Je wilt dit gerecht dus toevoegen aan de databank. We geven hieronder de volledige omschrijving. Jij moet in de juiste tabellen de juiste regels toevoegen. Informatie die al in de databank aanwezig is, mag je niet nog eens toevoegen, want dan antwoordt de databankserver met een foutmelding. Als er informatie ontbreekt die toch nodig is, dan verzin je zelf iets passends. Opgepast: omdat je enkel SELECT rechten hebt op de databank, zal je de queries niet kunnen uittesten.

We voegen ‘Kaaskroket met ham en asperges’ toe. Dit gerecht wordt beschreven als ‘Asperges in een kroket, samen met ham en Flandrienkaas’. Het kost een uur om dit gerecht te bereiden. Je hebt volgende ingrediënten nodig:

10 asperges (per stuk levert die een energie van 18 kcal)
150 g boter (100 g boter levert 737 kcal)
één citroen (1 citroen heeft 35 kcal energie)
200 g ham (100 g ham levert 335 kcal)
0,3 kg Flandrienkaas (100 g levert 365 kcal energie)

Dit gerecht past in de thema’s ‘klassieker’ en ‘Belgisch’. Schrijf alle benodigde queries om al deze informatie toe te voegen aan de databank.

Qua ingrediënten moet je enkel de Flandrienkaas toevoegen:

INSERT INTO kookclub.ingrediënt VALUES ('Flandrienkaas', 'g', 365)

Vervolgens nieuw gerecht toevoegen:

INSERT INTO kookclub.gerecht VALUES 
  ('Kaaskroket met ham en asperges','Asperges in een kroket, samen met ham en Flandrienkaas', 60);

Vervolgens de tussentabel tussen beide:

INSERT INTO kookclub.ingrediënt_in_gerecht VALUES ('Kaaskroket met ham en asperges','Asperge',10);
INSERT INTO kookclub.ingrediënt_in_gerecht VALUES ('Kaaskroket met ham en asperges','Boter',150);
INSERT INTO kookclub.ingrediënt_in_gerecht VALUES ('Kaaskroket met ham en asperges','Citroen',1);
INSERT INTO kookclub.ingrediënt_in_gerecht VALUES ('Kaaskroket met ham en asperges','Ham',200);
INSERT INTO kookclub.ingrediënt_in_gerecht VALUES ('Kaaskroket met ham en asperges','Flandrienkaas',300);

Thema klassieker bestaat nog niet, dus eerst toevoegen:

INSERT INTO kookclub.thema VALUES ('klassieker','een gouwe ouwe');

Tenslotte belgisch en klassieker koppelen:

INSERT INTO kookclub.gerecht_past_in_thema VALUES ('Kaaskroket met ham en asperges','belgisch');
INSERT INTO kookclub.gerecht_past_in_thema VALUES ('Kaaskroket met ham en asperges','klassieker');

SQL query's, deel 3 (+ video)

Schrijf de SQL-query die volgend overzicht genereert: een lijst van alle gemeentes met in totaal drie inschrijvingen voor kookworkshops. We willen alleen die gemeenten die minstens drie maal de kleine letter ‘e’ bevatten in de naam. Rangschik het resultaat in omgekeerd alfabetische volgorde.

Alternatief: ook groeperen op gemeente en postcode mag ook, postcode is specifieker.

SELECT G.postcode, COUNT(*) AS "aantal deelnames", G.gemeente
FROM gemeente G
  INNER JOIN lid L ON G.postcode = L.postcode
  INNER JOIN deelname D ON L.lidnr = D.lid
WHERE gemeente LIKE '%e%e%e%'
GROUP BY G.postcode
HAVING COUNT(*) = 3
ORDER BY 3 DESC

Stel: je maakt alle gerechten die als thema ‘Italiaans’ of ‘BBQ’ (of beide samen) hebben. Maak nu een overzicht per ingrediënt met per lijn de naam, hoeveelheid energie (in kcal per stuk, teentje, 100 g, 100 ml, …), de eenheid waarin geteld wordt, de totale hoeveelheid van dit ingrediënt die je nodig hebt om al deze gerechten klaar te maken en de hoeveelheid energie in kcal die dat vertegenwoordigt. Orden zo dat het ingrediënt met het grootste hoeveelheid kcal bovenaan staat. Schrijf de query.

SELECT ingrediënt, energie, eenheid, sum(hoeveelheid) AS "totale hoeveelheid",
  CASE
    WHEN eenheid = 'g' or eenheid = 'ml' THEN energie*sum(hoeveelheid)/100
    ELSE energie*sum(hoeveelheid)
  END AS "totale energie"
FROM ingrediënt_in_gerecht IG
  INNER JOIN gerecht_past_in_thema GT ON IG.gerecht = GT.gerecht
  INNER JOIN ingrediënt I ON I.naam = IG.ingrediënt
WHERE thema IN ('Italiaans','BBQ')
GROUP BY ingrediënt, energie, eenheid
ORDER BY 5 DESC