[SQL] Uitleg aan leerlingen

[Meijuh]

Ik heb de volgende versimpelde tabel 'koe'.

id (int)

productie (int)

Nu is de vraag aan de leerlingen in mijn klas (die ik les geef). Geef een lijst van de koeien die een bovengemiddelde productie hebben. Ik kom tot de volgende oplossing, maar ik vraag me af of dit technisch/wiskundig gezien goed is.

SQL:

SELECT k1.id FROM koe AS k1 WHERE k1.productie > (SELECT AVG(k2.productie) FROM koe k2)

Gelieve geen antwoord te geven als: "ja dit is goed want dit kan in MySQL".

Ik zit namelijk met het volgende probleem.
Een SELECT query geeft een tabel terug. Deze tabel vergelijk ik met een kolom, namelijk k1.productie. Het lijkt me eigenlijk onmogelijk om een kolom te vergelijken met een tabel, je vergelijkt namelijk ook geen String met een boolean in bijv. JAVA. Nu snap ik dat SQL op runtime kan kijken of de tweede SELECT één regel kan opleveren, maar dit kan SQL niet bekijken op compile-time.

Stel als ik dit probleem even vertaal naar verzamelingsleer. Kan ik een element uit een verzameling vergelijken met een verzameling? Én, kan ik een element uit een verzameling vergelijken met een verzameling waar een aggregatie-functie op los is gelaten?

Mocht één van deze vragen "ja" als antwoord hebben, dan wil ik de leerlingen bovenstaand antwoord wel uitleggen, anders niet. Of is er nog een andere uitwerking op deze vraag? Mocht er ook geen andere - eenvoudige - uitwerking zijn, dan denk ik dat ik de vraag schrap.

[hostname]

Meijuh schreef op maandag 17 januari 2011 @ 21:02:
...
Stel als ik dit probleem even vertaal naar verzamelingsleer. Kan ik een element uit een verzameling vergelijken met een verzameling? Én, kan ik een element uit een verzameling vergelijken met een verzameling waar een aggregatie-functie op los is gelaten?
...

De SELECT AVG levert een enkel element, een constante, op en geen verzameling. Vervolgens wordt elke regel uit de verzameling k1 vergeleken met de constante, wat gewoon kan. Je vergelijkt niet 2 verzamelingen, maar elk element uit de verzameling (k1) met een constante (in dit geval AVG(k2.productie)).

[ Voor 5% gewijzigd door hostname op 17-01-2011 21:14 ]

[Meijuh]

hostname schreef op maandag 17 januari 2011 @ 21:14:
[...]

De SELECT AVG levert een enkel element, een constante, op en geen verzameling. Vervolgens wordt elke regel uit de verzameling k1 vergeleken met de constante, wat gewoon kan. Je vergelijkt niet 2 verzamelingen, maar elk element uit de verzameling (k1) met een constante (in dit geval AVG(k2.productie)).

Ok, thx voor het antwoord.
Stel nu dat ik de tabel koe uitbreid met geboortejaar.
En ik groepeer nu op geboortejaar ( in de tweede query).
Dan krijg ik dus een verzameling groepen van het geboortejaar.
Dan mag ik dus niet meer k1.productie vergelijken met AVG(k2.productie), omdat ik dan een kolom vergelijk met een tabel?

[Creepy]

Niet om het 1 en ander, maaruh die GROUP BY staat prima uitgelegd in onze SQL FAQ: Programming FAQ - SQL

En inderdaad, als je binnenste query meer dan 1 resultaat kan teruggeven, dan kan je niet meer direct vergelijken. Je zou ook gewoon een foutmelding moeten krijgen. Waarom heb je dat niet gewoon uitgeprobeerd, jij bent hier de docent, niet wij Dus jij moet je zeker in de materie verdiepen.

offtopic:
SQL les door Creepy en RobIII, hoeveel leerlingen zouden dat willen en wat levert het ons op?

[ Voor 16% gewijzigd door Creepy op 17-01-2011 21:39 ]

[RobIII]

^ Wat hij zegt.

Meijuh schreef op maandag 17 januari 2011 @ 21:26:

Dan mag ik dus niet meer k1.productie vergelijken met AVG(k2.productie), omdat ik dan een kolom vergelijk met een tabel?

Alles mag; of het zinnig/nuttig is is een tweede (los van dat dit dus niet, zoals je het nu stelt, werkt). Maar wat let je om in het vergelijk ook het geboortejaar mee te nemen? Dan heb je een gemiddelde productie per geboortejaar waar je alle koeien weer met hun eigen geboortejaar tegenaan kunt houden.

Wat ik me afvraag (en dit bedoel ik niet lullig) is waarom je les geeft in dergelijke stof terwijl je het zelf niet (goed) beheerst. Waar komt zoiets vandaan als ik vragen mag? Hoe ontstaat zo'n situatie?

[ Voor 4% gewijzigd door RobIII op 17-01-2011 21:40 ]

[farlane]

Wat ik me afvraag (en dit bedoel ik niet lullig) is waarom je les geeft in dergelijke stof terwijl je het zelf niet (goed) beheerst.

Tis iig beter dan iemand die net doet alsof en de arme leerlingen wat op de mouw spelt

[D-Raven]

Moet zeggen, vind wel dat ie ballen heeft om als docent hier vragen te gaan stellen. Beter dat hij vragen stelt op een tech fora, dan bullshit gaat zitten verkopen aan zijn leerlingen.

[Jrz]

RobIII schreef op maandag 17 januari 2011 @ 21:37:
^ Wat hij zegt.

[...]

Wat ik me afvraag (en dit bedoel ik niet lullig) is waarom je les geeft in dergelijke stof terwijl je het zelf niet (goed) beheerst. Waar komt zoiets vandaan als ik vragen mag? Hoe ontstaat zo'n situatie?

Omdat lesgeven een apart vak is?

Liever een goede docent met minder kennis, dan iemand met veel kennis die daar niets van weet over te dragen

[RobIII]

Jrz schreef op maandag 17 januari 2011 @ 22:13:
[...]


Omdat lesgeven een apart vak is?

Liever een goede docent met minder kennis, dan iemand met veel kennis die daar niets van weet over te dragen

Dat vroeg ik toch helemaal niet? _{En ik heb ook nergens beweerd dat z'n vraag niet welkom is en ik ben 't eens met de posters hierboven dat 't vast wat moed heeft gekost hier een topic over te openen.}

[YopY]

Euh. Als je leerlingen het moeten oplossen (en ik neem aan dat je het ze niet gaat voorkauwen) voer je toch hun SQL queries uit en kijk je welke het beste is? Laat hun het werk maar doen, goed mogelijk dat ze zelf betere oplossingen bedenken dan je zelf doet . (niet als flame bedoeld overigens)

[CH4OS]

YopY schreef op maandag 17 januari 2011 @ 22:29:
Euh. Als je leerlingen het moeten oplossen (en ik neem aan dat je het ze niet gaat voorkauwen) voer je toch hun SQL queries uit en kijk je welke het beste is? Laat hun het werk maar doen, goed mogelijk dat ze zelf betere oplossingen bedenken dan je zelf doet . (niet als flame bedoeld overigens)

Maar wát is dan 'het beste'? Er zijn, zeker met programmeren en SQL en dergelijken, meerdere wegen die naar Rome gaan.

[cariolive23]

Dit soort dingen zijn toch altijd prima met een JOIN op te lossen? Krijg je gewoon duidelijk omschreven verzamelingen en daar kun je dan prima vergelijkingen mee gaan doen.

SQL:

SELECT 
  k1.id,
  k1.bouwjaar
FROM 
  koe AS k1 -- verzameling 1
    JOIN (SELECT AVG(productie) AS gemiddeld, bouwjaar FROM koe GROUP BY bouwjaar) AS k2 -- verzameling 2
      ON k1.bouwjaar = k2.bouwjaar -- relatie tussen beide verzamelingen
WHERE 
  k1.productie > k2.gemiddeld

Ps. Leer jouw leerlingen ook direct het netjes inspringen van de SQL, dat houdt de boel leesbaar en dus begrijpelijk.

Pps. MySQL wijkt op vele plaatsen flink af van de SQL standaarden, ik mag toch hopen dat ze daar geen SQL mee hoeven te leren. En mocht dat toch zo zijn, gebruik dan wel de ANSI-SQL mode, dan doet zelfs MySQL zijn best om correcte SQL te gebruiken.

[Meijuh]

CptChaos schreef op maandag 17 januari 2011 @ 22:40:
[...]
Maar wát is dan 'het beste'? Er zijn, zeker met programmeren en SQL en dergelijken, meerdere wegen die naar Rome gaan.

Ja precies. De query werkt prima in MySQL en ik heb hem ook wel geprobeerd.
Edit @ cariolive23 idd, ik wil leerlingen SQL uitleggen en niet het dialect van MySQL. Ik zal eens kijken naar dat ANSI-SQL.
Er zal geen enkele leerling mijn antwoord hierboven in twijfel trekken. Het gaat erom dat ik de essentie goed uitleg aan de leerling. Sommige mensen hierboven begrijpen mijn probleem helemaal niet.


En ik loop nu 8 weken stage op een middelbare school en ik zeg dus helemaal niet dat ik docent ben. Ik ben dus sinds een paar weken een DIO.

Ps. Leer jouw leerlingen ook direct het netjes inspringen van de SQL, dat houdt de boel leesbaar en dus begrijpelijk.

Dat doe ik zeker, alleen niet hier op het forum (A).

SQL:

SELECT 
  k1.id,
  k1.bouwjaar
FROM 
  koe AS k1 -- verzameling 1
    JOIN (SELECT AVG(productie) AS gemiddeld, bouwjaar FROM koe GROUP BY bouwjaar) AS k2 -- verzameling 2
      ON k1.bouwjaar = k2.bouwjaar -- relatie tussen beide verzamelingen
WHERE 
  k1.productie > k2.gemiddeld

Dit is inderdaad wel een goede oplossing. Maar dit ga ik niet eens proberen uit te leggen aan 6V.

Maar als het inderdaad zo is in standaard SQL dat AVG(productie) een constante oplevert en AVG(productie) met bouwjaar als groepen een verzameling, dan is dat antwoord voor mij voldoende.

Ik ga eens kijken wat het boek van gegevensbanken zegt over het omzetten van dit vraagstuk naar verzamelingsnotatie.

Edit: uit eenzelfde voorbeeld uit het boek blijkt de oplossing van mij goed te zijn. AVG(productie) geeft inderdaad een constante en geen tabel.

edit2: stel nu dat de tabel koe leeg is dan lijkt me niet dat AVG(productie) een getal kan opleveren. Het lijkt me logisch dat AVG(productie) dan NULL oplevert. Maar koe.productie > NULL kan toch geen true of false opleveren? Hoe gaat SQL hier mee om?

[ Voor 55% gewijzigd door Meijuh op 18-01-2011 08:56 ]

[Killemov]

Meijuh schreef op maandag 17 januari 2011 @ 23:10:
[...]
edit2: stel nu dat de tabel koe leeg is dan lijkt me niet dat AVG(productie) een getal kan opleveren. Het lijkt me logisch dat AVG(productie) dan NULL oplevert. Maar koe.productie > NULL kan toch geen true of false opleveren? Hoe gaat SQL hier mee om?

Dit is weer basis werk, even opzoeken dus ... Alle vergelijkingen met NULL leveren (in principe!) NULL op.

[Tanuki]

Het is wel lullig dat in het geval van jouw (Meijuh) oplossing er 1 + (aantal koeien) query's worden gedraaid.

Er wordt sowieso 1 query gedraaid die alle koeien ophaalt die boven het gemiddelde zitten, maar voor elke koe wordt een query gedraaid om het gemiddelde op te halen en dan vervolgens met dit resultaat te kijken of de koe er boven zit of niet. Dat is qua performance wel een flinke slag teruguit, ookal ga je dit nauwelijks merken met een klein aantal koeien.

Ik zou daarom liever voor de oplossing van cariolive23 gaan. Al weet ik niet helemaal precies waarom die vergelijking tussen de bouwjaren erin staat?

Mij lijkt deze query beter:

SQL:

SELECT k1.id, k1.bouwjaar FROM koe AS k1, (SELECT AVG(productie) AS gemiddeld FROM koe) AS k2 WHERE k1.productie > k2.gemiddeld;

Hierbij worden 2 query's uitgevoerd, 1 om het gemiddelde op te halen en 1 om alle koeien op te halen die boven het gemiddelde zitten: Precies wat je vraagt.

[DamadmOO]

l0c4lh0st schreef op donderdag 20 januari 2011 @ 00:41:
Het is wel lullig dat in het geval van jouw (Meijuh) oplossing er 1 + (aantal koeien) query's worden gedraaid.

Er wordt sowieso 1 query gedraaid die alle koeien ophaalt die boven het gemiddelde zitten, maar voor elke koe wordt een query gedraaid om het gemiddelde op te halen en dan vervolgens met dit resultaat te kijken of de koe er boven zit of niet. Dat is qua performance wel een flinke slag teruguit, ookal ga je dit nauwelijks merken met een klein aantal koeien.

Dus echt niet. Vraag maar eens de explain/query plan op. Als eerste zal de subquery die in de where class gedraaid worden doordat er geen verwijzing naar de "outer" query is. En het resultaat hiervan zal gewoon gebruikt worden in die "outer" query. Dus waar je die "1 + (aantal koeien)" query's vandaan haalt is mij een raadsel.

[Hydra]

Uit nieuwsgierigheid: Krijgen leerlingen op 6VWO SQL? Zijn dat dan alle leerlingen of is dat een keuze?

[Verwijderd]

@Hydra: Bij ons, en ik ga er vanuit overal, was dit een keuze. Je kon namelijk informatica als keuzevak kiezen. In de 4e begonnen we dan met een website (HTML/CSS), in de 5e leerde je MySQL om vervolgens in de 6e je website met behulp van PHP aan de database te koppelen.

[Gimmeabrake]

Hydra schreef op donderdag 20 januari 2011 @ 09:28:
Uit nieuwsgierigheid: Krijgen leerlingen op 6VWO SQL? Zijn dat dan alle leerlingen of is dat een keuze?

Ik ben pas 2 jaar van het vwo af, en ik kan wat Melay zegt bevestigen. SQL zit eigenlijk bij elke school wel in het pakket van het keuzevak informatica in de tweede fase. In mijn geval werkten we overigens met OpenOffice Base, wat ik persoonlijk een gruwelijk dialect van SQL vind hebben. Maar goed, het belangrijkste is dat leerlingen door krijgen hoe zo'n database in elkaar steekt. Mochten ze het interessant vinden en ermee door willen, is de overstap naar [vul-in]SQL maar een kleine moeite. SQL werd overigens in 5vwo behandeld. In 4vwo begonnen we met Logo en NSBasic, 5vwo nog steeds NSBasic en dus ook SQL, en in 6vwo SQL en Delphi.

Edit om ook nog maar even on-topic te gaan: Groots respect van mij om als docent (of je in opleiding bent of niet maakt me niet uit) gewoon dit soort vragen te stellen hier. Veel docenten hebben de eigenschap heel betweterig te zijn, en raken geïrriteerd als leerlingen dit (vaak terecht) in twijfel te trekken. Mijn informatica-docent wist ook alles het beste, terwijl ik er vaker aan twijfelde of het klopte wat hij zei. Toen ik bij hem in de klas zat gaf ik hem het voordeel van de twijfel(afgezien van mijn iets mindere kennis 2 jaar geleden ook grotendeels beïnvloed door zijn voordeel van de macht over mijn cijfers), inmiddels weet ik wel beter. Liever een docent die af en toe zegt: "ik weet het niet zeker, ik zoek het voor je op", dan een docent die halve waarheden verkoopt.

[ Voor 31% gewijzigd door Gimmeabrake op 20-01-2011 11:21 ]

[Meijuh]

gerrymeistah schreef op donderdag 20 januari 2011 @ 11:14:
[...]

Ik ben pas 2 jaar van het vwo af, en ik kan wat Melay zegt bevestigen. SQL zit eigenlijk bij elke school wel in het pakket van het keuzevak informatica in de tweede fase. In mijn geval werkten we overigens met OpenOffice Base, wat ik persoonlijk een gruwelijk dialect van SQL vind hebben. Maar goed, het belangrijkste is dat leerlingen door krijgen hoe zo'n database in elkaar steekt. Mochten ze het interessant vinden en ermee door willen, is de overstap naar [vul-in]SQL maar een kleine moeite. SQL werd overigens in 5vwo behandeld. In 4vwo begonnen we met Logo en NSBasic, 5vwo nog steeds NSBasic en dus ook SQL, en in 6vwo SQL en Delphi.

Informatica is voor scholen niet verplicht om aan te bieden, als ze het aanbieden is het een keuzevak voor de leerlingen. Voor VWO 480 uur en voor HAVO 320 uur. Voor Informatica is er geen centraal examen dus, docenten zijn redelijk vrij om te kiezen wat ze willen behandelen. Wel is er door één of andere instelling een handreiking geschreven met wat een docent zou moeten behandelen. Maar daarin wordt met termen als databases en computerarchitectuur gegooid. In het behandelen van deze onderwerpen is de docent dus heel vrij.

Edit om ook nog maar even on-topic te gaan: Groots respect van mij om als docent (of je in opleiding bent of niet maakt me niet uit) gewoon dit soort vragen te stellen hier. Veel docenten hebben de eigenschap heel betweterig te zijn, en raken geïrriteerd als leerlingen dit (vaak terecht) in twijfel te trekken. Mijn informatica-docent wist ook alles het beste, terwijl ik er vaker aan twijfelde of het klopte wat hij zei. Toen ik bij hem in de klas zat gaf ik hem het voordeel van de twijfel(afgezien van mijn iets mindere kennis 2 jaar geleden ook grotendeels beïnvloed door zijn voordeel van de macht over mijn cijfers), inmiddels weet ik wel beter. Liever een docent die af en toe zegt: "ik weet het niet zeker, ik zoek het voor je op", dan een docent die halve waarheden verkoopt.

Thx Altijd kritisch op jezelf zijn zeg ik altijd, dan leer je het meest.