Domeinmodellering/2 Activiteiten/Assignments/Opdracht02-B02/4 Extra oefening voor toets
Inhoud
Een alternatieve ‘sample database’ om het opstellen van SQL-queries nog eens extra mee te oefenen …
Voor als je nog wat extra wilt oefenen met het opstellen van SQL-queries geven we hier een schets van een 'papieren [dus niet echt, fysiek bestaande] database'.
Dat heeft als voordeel, dat je niet in staat bent om aan de verleiding toe te geven om niet te gaan nadenken, maar maar wat uit te proberen (hacken) achter het toetsenbord.
Je zult je voor het maken van deze opdracht puur moeten baseren op: - je kennis van de mogelijke constructies voor SQL-queries - en je analyse van het probleem.
Let op: het is NIET de bedoeling dat degenen die 'goed zijn' in het opstellen van SQL-queries, hier deze vragen gaan beantwoorden. Het is net WEL de bedoeling dat degenen die zich zelf niet zo sterk vinden in het opstellen van SQL-queries hier kunnen pogen een goed antwoord te formuleren en als dan 'de beteren' zo'n uitwerking zien en het idee hebben dat ze er een positieve feedback (kortom: op een prettige wijze) ter ondersteuning/correctie op kunnen geven, dat dan inderdaad doen.
Tabelstructuren
Beschouw de volgende structuur en 'mini-invulling' van een relationele database.
Deze database bestaat uit 4 tabellen, "Klanten", "Vertegenwoordigers", "Producten" en "Verkopen"; van elke tabel worden hieronder enkele gevulde rijen weergegeven.
| Klant_nr | Naam | Adres | Plaats | Postcode |
|---|---|---|---|---|
| 001 | Janssen, J | Annastraat 456 | Nijmegen | 6525 ZP |
| 002 | Klaassen, K | Groenestraat 48 | Nijmegen | 6531 HS |
| 003 | Pietersen, P | Dennenweg 147 | Arnhem | 6823 MR |
| Vert_nr | Naam | Geslacht | Provisie | Salaris |
|---|---|---|---|---|
| 001 | Arendsen, A | M | 10% | 90000 |
| 002 | Zeeuw, Z de | M | 20% | 40000 |
| 003 | Janssen, J | V | 20% | 50000 |
N.B. in werkelijkheid staan hierboven in de 'vertegenwoordigers'-tabel in de kolom 'provisie' numerieke waarden als 0.1 (voor 10%) en 0.2 (voor 20%) e.d.
| Prod_nr | In_voorraad | Stukprijs | Gewicht |
|---|---|---|---|
| 001 | 54 | 99.99 | 12.5 kg |
| 002 | 72 | 238.99 | - |
| 003 | 458 | 50.00 | 1.3 kg |
N.B. In de 'producten'-tabel hoeft 'gewicht' niet ingevuld te zijn (in feite zijn waarden in deze kolom numeriek; voor de leesbaarheid is hier 'kg' toegevoegd).
| Verkoop_nr | Prod_nr | Aantal | Bedrag | Vert_nr | Klant_nr |
|---|---|---|---|---|---|
| 001 | 001 | 1 | 99.99 | 001 | 002 |
| 002 | 001 | 2 | 199.98 | 002 | 001 |
| 003 | 002 | 1 | 238.99 | 001 | 003 |
(N.B. de getoonde database is een variant op: http: // www.extropia. com/ tutorials/sql/create.html )
De vragen bijelkaar
De vragen die je met de hulp en het commentaar van anderen hier kunt beantwoorden zijn resp. :
- Geef voor de beide geslachten 'vertegenwoordigers' (dus: 'per geslacht') aan, voor hoeveel ze in totaal verkochten.
- Aan welke klanten zijn producten verkocht waarvan in de database geen 'gewicht' is opgenomen. Toon de namen en volledige adressen van die klanten.
- Toon per vertegenwoordiger het totale provisie-bedrag dat hij/zij voor het totaal van zijn/haar verkopen krijgt.
- Geef van het product, waarvan voor de grootste totaalwaarde ('totaalbedrag') artikelen in voorraad zijn, het productnummer en die totaalwaarde. (N.B. als er meerdere producten zijn met eenzelfde 'hoogste totaalwaarde', dan moeten van al die 'meerdere producten' de gevraagde gegevens getoond worden.)
- Geef een overzicht van alle klanten (toon hun namen) waaraan minder dan 3 verkopen hebben plaatsgevonden.
- Toon van de vertegenwoordigers de gegevens van degenen die per geslacht het hoogste salaris hebben. Hint: gebruik een correlated query-constructie.
- Van welke bestaande producten zijn nog geen verkopen geregistreerd en is het gewicht méér dan 10 kg?
- Toon de gegevens van de vertegenwoordigers die in totaal meer dan 5 verschillende producten aan klanten hebben verkocht.
- Aan klanten uit welke stad zijn voor het hoogste totaalbedrag producten verkocht? Toon de naam van die stad en het bijbehorende [hoogste] totaalbedrag.
- Geef per stad aan, aan welke klant uit die stad het meest (in geld) verkocht is. Toon naast de naam van elke stad ook de naam van die 'beste' klant.
Aanpak
Bestudeer uiteraard eerst opnieuw de in de colleges behandelde stof. Kijk beslist ook naar de op het college/werkcollege besproken sheets over 'foutieve queries'.
Stel pas daarna SQL-queries op om een antwoord te vinden op de hiernavolgende informatievragen. Ga systematisch te werk en analyseer je probleem eerst op o.a.:
- wat moet getoond worden?
- condities?
- vereiste tabellen?
en vraag je pas daarna af welke SQL-constructen gebruikt moeten worden, zoals:
- Joins ? ('simple', left -, right -?)
- Subqueries ? (correlated?)
- WHERE .. ? ORDER BY ?
- GROUP BY ? HAVING ?
- >= ALL .. ? … NOT IN … ?
- Views / assimilated queries / others ?
De afzonderlijke vragen met mogelijke antwoorden en feedback erop:
Nogmaals: laat het beantwoorden van de vragen over aan degenen die zichzelf niet zo goed vinden in het opstellen van SQL-queries en laat degenen die daar wel goed in zijn, zich beperken tot het geven van suggesties/feedback.
Je kunt hier dus in samenwerking met anderen tot een correct antwoord (SQL-query) komen!
Klik gewoon bij de betreffende vraag linksboven op 'bewerken' en tik je bijdrage in.
Vraag 1
1. Geef voor de beide geslachten 'vertegenwoordigers' (dus: 'per geslacht') aan, voor hoeveel ze in totaal verkochten.
Antwoord:
<source lang='sql'>
Select ( naam, geslacht, aantal
from vertegenwoordigers,verkopen
where vertegenwoordigers.vert_nr = verkopen.vert_nr
group by naam,geslacht,aantal)</source>
Ik denk niet dat dit helemaal de bedoeling van de vraag was. Wat ik eruit op kan maken, is dat men op zoek is naar:
Ik zou dus verwachten dat er iets uit moet komen in de richting van: GESLACHT TOTAAL_BEDRAG_VERKOPEN M 538.96 V 0 Hier is in ieder geval--zoals je zelf al goed had gezien--een join tussen de Nog een kleine opmerking: de haakjes achter je |
 |
|
| Tom Sanders → Domeinmodellering | Remove this comment when resolved! | |
| Ik ben er ook naar aan het kijken, en ik vraag me af of je een Left Join moet gebruiken omdat het ook kan zijn dat een geslacht niets heeft verkocht, maar je dat wel wilt zien. Dus ik kom op het volgende uit: |  |
|
| Ben Brücker → Domeinmodellering | Remove this comment when resolved! | |
<source lang='sql'>
Select Geslacht, Sum (Bedrag * Aantal)
From Vertegenwoordiger V Left Join Verkopen K
On V.Vert_nr = K.Vert_nr
Group By Geslacht</source>
Daar zul je inderdaad een LEFT JOIN voor nodig hebben. Ik denk overigens dat de waarde voor VERKOPEN.BEDAG al het totaalbedrag (aantal * stukprijs) is: in de voorbeeldpopulatie bij VERKOPEN staat bij de rij met Verkoop_nr 002 een bedrag van 199.98 voor 2 producten met stukprijs 99.99. Als dat inderdaad zo is, dan zou het dus simpelweg SUM(Bedrag) moeten zijn. |
|
|
| Tom Sanders → Domeinmodellering | Remove this comment when resolved! | |
Vraag 2
2. Aan welke klanten zijn producten verkocht waarvan in de database geen 'gewicht' is opgenomen. Toon de namen en volledige adressen van die klanten.
Antwoord:
<source lang = 'sql'>
select (naam,adres,plaats,postcode
from klanten,verkopen,producten
where klanten.klant_nr = verkopen.klant_nr
AND verkopen.prod_nr =producten.prod_nr
AND producten.gewicht is null
group by naam, adres, plaats, postcode)</source>
Hier wederom de opmerking m.b.t. de haakjes, maar voor de rest ziet hij er goed uit! In plaats van de GROUP BY naam, adres, plaats, postcode, zou je misschien SELECT DISTINCT naam, adres, plaats, postcode kunnen gebruiken; dat vind ik altijd een wat nettere oplossing. |
|
|
| Tom Sanders → Domeinmodellering | Remove this comment when resolved! | |
Antwoord met subqueries i.p.v. joins:
<source lang = 'sql'>
SELECT NAAM, ADRES, POSTCODE, PLAATS
FROM KLANTEN
WHERE KLANT_NR IN (SELECT KLANT_NR
FROM VERKOPEN
WHERE PROD_NR IN (SELECT PROD_NR
FROM PRODUCTEN
WHERE GEWICHT IS NULL))</source>
Dit kan ook lijkt me toch? Ik vind persoonlijk subqueries veel makkelijker werken dan joins.
Vraag 3
3. Toon per vertegenwoordiger het totale provisie-bedrag dat hij/zij voor het totaal van zijn/haar verkopen krijgt.
Antwoord:
<source lang = 'sql'>
Select Distinct Naam, Sum (Provisie * Bedrag * Aantal)
From Vertegenwoordigers V, Verkopen K
Where V.Vert_nr = k.Vert_nr
</source>
| Voordat ik begin over wat hier nou eigenlijk gevraagd wordt is het belangrijk om eerst de syntax van de query te controleren. Kun je je nog herinneren wanneer je een GROUP BY moest gebruiken? Ik zal een hint geven, het heeft namelijk te maken met functies zoals COUNT, SUM etc. Daarnaast wordt hier gevraagd naar het totaal van de vertegenwoordiger zijn/haar verkopen. Een vertegenwoordiger verkoopt meerdere (en verschillende) producten. Je moet dus het totale bedrag van één vertegenwoordiger gebruiken die uit het totaal van meedere producten bestaat. |  |
|
| Bas Elbers → Domeinmodellering | Remove this comment when resolved! | |
| Bovendien geldt, dat je -bij géén gebruik van een GROUP BY- géén combinatie van kolomnamen en statistische functie in je SELECT-regel mag gebruiken [iets waar jij je niks van aantrekt], mede omdat dat betekenisloos is. |  |
|
| Ger Paulussen → Domeinmodellering | Remove this comment when resolved! | |
| {{{3}}} |  |
|
| Lars Bade → Domeinmodellering | Remove this comment when resolved! | |
| De opmerking hierboven over twee vertegenwoordigers met dezelfde naam, is in principe heel erg correct, maar hier ietwat onlogisch omdat in de SELECT-regel gevraagd wordt om de naam... Daarom zou ikzelf ook alleen groeperen op naam. | |
|
| Ger Paulussen → Domeinmodellering | Remove this comment when resolved! | |
Vraag 4
4. Geef van het product, waarvan voor de grootste totaalwaarde ('totaalbedrag') artikelen in voorraad zijn, het productnummer en die totaalwaarde. (N.B. als er meerdere producten zijn met eenzelfde 'hoogste totaalwaarde', dan moeten van al die 'meerdere producten' de gevraagde gegevens getoond worden.)
Antwoord:
<source lang='sql'>
Select Distinct Prod_nr, (In_Voorraad * stukprijs)
From Producten
Where (In_voorraad * stukprijs) = MAX(In_voorraad * stukprijs)
</source>
| Kijk eens naar de sheets van de werkcollege van maandag. Het heeft te maken met de MAX in je WHERE-clausule (2e sheet 2e voorbeeld). Ikzelf vroeg me af of dit met een VIEW kon vanwege de N.B.?) | |
|
| Bas Elbers → Domeinmodellering | Remove this comment when resolved! | |
| Ik zat te denken dat de statistische functie wel kon als je het als een vergelijking gebruikt waar een boolean uitkomt. Maar je hebt natuurlijk gelijk gezien dit hier alleen van toepassing is op een enkele rij. | |
|
| Ben Brücker → Domeinmodellering | Remove this comment when resolved! | |
Dit was mijn poging:
SELECT Prod_nr, SUM(In_Voorraad * Stukprijs) FROM PRODUCTEN GROUP BY Prod_nr HAVING SUM(In_Voorraad * Stukprijs) >= ALL (SELECT SUM(In_Voorraad * Stukprijs) FROM PRODUCTEN GROUP BY Prod_nr)
Wat valt er te zeggen over onderstaande query?
CREATE VIEW totaal_artikelen (Product_nr, totaalbedrag) AS SELECT Prod_nr, Stukprijs*In_voorraad FROM PRODUCTEN SELECT Prod_nr, MAX(totaal_bedrag) FROM totaal_artikelen GROUP BY Prod_nr
| De tweede query met de view lijkt mij wel goed. Ik weet niet of dat bijster efficiënt is, maar ik vind het wel elegant. Klein schoonheidsfoutje is alleen dat je totaalbedrag een keer met _ en een keer zonder hebt. Kun je de having van de andere query niet gewoon maken als: | |
|
| Ben Brücker → Domeinmodellering | Remove this comment when resolved! | |
HAVING SUM(In_Voorraad * Stukprijs) = MAX (SUM(In_Voorraad * Stukprijs))
Ik heb het als volgt probeert en volgens mij klopt dat zo, of niet?
| Schoonheidsfoutjes kosten je het tentamen niet, maar wel goed gezien ;). Voor jouw suggestie verwijs ik je graag naar de standaardfouten die Ger liet zien op het werkcollege maandag (Some SQL-queries with errors op BB, pagina 4 en 5 van de sheets). Hou gewoon aan dat je nóóit iets als MAX(COUNT) of AVG(MAX) of wat dan ook gebruikt, nergens niet. Het enige wat wél kan is dus éérst een VIEW maken en dan de MAX van die VIEW nemen (maar dan kan het ook zonder VIEW!). | |
|
| Bas Elbers → Domeinmodellering | Remove this comment when resolved! | |
<source lang='sql'> SELECT Prod_nr, In_vooraad*Stukprijs AS totaalwaarde FROM Producten WHERE In_vooraad*Stukprijs >= ALL(SELECT In_voorraad*Stukprijs
FROM Producten)
</source>
| Ik ben hier ook geen expert in, maar volgens mij is dit inderdaad correct. Het is daarnaast een stuk korter (en als je het aan mij vraag beter) dan de mijne. Goedzo :D. | |
|
| Bas Elbers → Domeinmodellering | Remove this comment when resolved! | |
Vraag 5
5. Geef een overzicht van alle klanten (toon hun namen) waaraan minder dan 3 verkopen hebben plaatsgevonden.
Antwoord:
<source lang = 'sql'>
SELECT K.Naam
FROM Klanten K, Verkopen VK
WHERE K.Klant_nr = VK.Klant_nr
GROUP BY K.Klant_nr, K.Naam
HAVING COUNT(*) < 3
</source>
Hmm, wellicht dat dit equivalent is aan mijn onderstaande query, maar dat durf ik niet te zeggen.
SELECT Naam FROM KLANTEN WHERE Klant_nr IN (SELECT Klant_nr) FROM VERKOPEN GROUP BY Klant_nr HAVING COUNT(*) < 3) |
|
|
| Bas Elbers → Domeinmodellering | Remove this comment when resolved! | |
| Volgens mij zijn deze twee queries identiek, behalve van het feit, dat jouw oplossing een SubQuery bevat een de mijne een Join. | |
|
| Lars Bade → Domeinmodellering | Remove this comment when resolved! | |
Vraag 6
6. Toon van de vertegenwoordigers de gegevens van degenen die per geslacht het hoogste salaris hebben. Hint: gebruik een correlated query-constructie.
Antwoord: <source lang = 'sql'>
</source>
Vraag 7
7. Van welke bestaande producten zijn nog geen verkopen geregistreerd en is het gewicht méér dan 10 kg?
Antwoord:
<source lang='sql'>
</source>
Vraag 8
8. Toon de gegevens van de vertegenwoordigers die in totaal meer dan 5 verschillende producten aan klanten hebben verkocht.
Antwoord:
Dit is eigenlijk de enige query bij die ik erg onzeker ben of ze juist is.
<source lang='sql'>
SELECT *
FROM Vertegenwoordigers
WHERE Vert_nr IN (SELECT Vert_nr
FROM Verkopen
GROUP BY Vert_nr
HAVING COUNT(DISTINCT Prod_nr) > 5)
</source>
| Volgens zegt deze query exact naar wat er gevraagd wordt. Je kunt hier goed nagaan wat de query precies laat zien, maar wellicht kan iemand anders het nog bevestigen?) | |
|
| Bas Elbers → Domeinmodellering | Remove this comment when resolved! | |
Vraag 9
9. Aan klanten uit welke stad zijn voor het hoogste totaalbedrag producten verkocht? Toon de naam van die stad en het bijbehorende [hoogste] totaalbedrag.
Antwoord:
Dit is naar aanleiding van het commentaar van Lars bij opdracht 10. Ik snap niet hoe dit dan moet... Je moet groeperen in het 2e deel van de subquery, want je wil alles bij elkaar hebben geteld pér stad. Daarvoor heb je de tabel KLANTEN nodig, maar die kun je niet gebruiken omdat je SUM(Bedrag) met elkaar aant het vergelijken bent? Daarom gebruik ik dus een VIEW :).
<source lang='sql'>
SELECT Plaats,SUM(Bedrag) FROM VERKOPEN V, KLANTEN K WHERE V.Klant_nr = K.Klant_nr GROUP BY K.Plaats HAVING SUM(Bedrag) >= ALL
(SELECT SUM(Bedrag)
FROM VERKOPEN V2
WHERE V2.Klant_nr = K.Klant_nr
GROUP BY K.Plaats)
Deze laatste GROUP BY mag niet, hij zou K.Plaats niet herkennen(?) en dus probeer ik:
SELECT Plaats,SUM(Bedrag) FROM VERKOPEN V, KLANTEN K WHERE V.Klant_nr = K.Klant_nr GROUP BY K.Plaats HAVING SUM(Bedrag) >= ALL
(SELECT SUM(Bedrag)
FROM VERKOPEN V2
WHERE V2.Klant_nr = (SELECT Klant_nr
FROM KLANTEN K2
GROUP BY Plaats, Klant_nr))
</source>
| {{{3}}} | |
|
| Lars Bade → Domeinmodellering | Remove this comment when resolved! | |
| Aha, je mag daar dus wel gewoon een JOIN gebruiken. Bedankt nog voor je reactie! Hopelijk is dit genoeg om het tentamen door te komen :). Zo vind ik het wel duidelijk, alhoewel ik het nog steeds raar vindt dat je groepeert op een andere tabel dan dat je eigenlijk laat zien. | |
|
| Bas Elbers → Domeinmodellering | Remove this comment when resolved! | |
| Dat maakt eigenlijk niet veel uit omdat je twee tabellen joint. Als je twee tabellen joint heb je in principe een heel groot tabel en je kunt op alle onderdelen van deze tabel grouperen. Welke kolommen je dann uiteindeljk in de select op de scherm laat verschijnen maakt niets meer uit volgens mij. | |
|
| Lars Bade → Domeinmodellering | Remove this comment when resolved! | |
Vraag 10
10. Geef per stad aan, aan welke klant uit die stad het meest (in geld) verkocht is. Toon naast de naam van elke stad ook de naam van die 'beste' klant.
Antwoord:
<source lang = 'SQL'>
Bij deze vind ik het te lastig om het zonder een VIEW te doen. Mijn query is als volgt:
CREATE VIEW meeste_geld (Klant_nr, Geld) AS SELECT Klant_nr,SUM(bedrag) FROM VERKOPEN GROUP BY Klant_nr
SELECT Naam, Plaats FROM KLANTEN K, meeste_geld M WHERE K.Klant_nr = M.Klant_nr AND Geld >= ALL (SELECT Geld
FROM meeste_geld M2 AND M2.plaats = K.plaats )
</source>
| De aanpak is goed en ik denk ook dat deze query naar het gewenste resultaat leid, maar volgens mij kan het ook makkelijker zonder een view. Je moet alleen de main-query naar de klant_nr grouperen en dan kun je ook in de HAVING-deel de som uitruikenen en in de Subquery natuurlijk ook. Dan heb je twee keer de berekening van de som, maar geen view nodig ;) | |
|
| Lars Bade → Domeinmodellering | Remove this comment when resolved! | |
