Domeinmodellering/2010-11/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 geslacht, SUM(bedrag)
FROM vertegenwoordigers VT, verkopen VK
WHERE VT.vert_nr = VK.vert_nr
GROUP BY geslacht
</source>
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 *
FROM klanten
WHERE klant_nr IN (SELECT klant_nr
FROM verkopen
WHERE prod_nr IN (SELECT prod_nr
FROM producten
WHERE prod_nr IS NULL))
</source>
| moet de laatste prod_nr niet gewicht zijn? | ||
| Marvin Barron → Domeinmodellering | Remove this comment when resolved! | |
| @Marvin Ik denk het wel |  |
|
| Carlo Geertse → Domeinmodellering | Remove this comment when resolved! | |
| Moet het wel IS NULL zijn er staat een '-' en geen leeg veld. |  |
|
| Menno van Wieringen → Domeinmodellering | Remove this comment when resolved! | |
| Ja, want de kolom GEWICHT bevat eigenlijk numerieke waarden. Er zal dus geen streepje staan in de 'echte' tabel. |  |
|
| Robin Munsterman → Domeinmodellering | Remove this comment when resolved! | |
mag dit ook?
<source lang = 'sql'>
SELECT Naam, Adres
FROM KLANTEN K, VERKOPEN V, PRODUCTEN P
WHERE K.kant_nr = V.klant_nr
AND V.Prod_nr = P.Prod_nr
AND Gewicht IS NULL
</source>
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 naam, provisie * SUM (bedrag)
FROM vertegenwoordigers vw, verkopen vk
WHERE vw.vert_nr = vk.vert_nr
GROUP BY naam
</source>
je moet toch ook het aantal meetellen? Ik dacht dit, maar ik weet het niet zeker: <source lang = 'sql'> SELECT naam, provisie * SUM (aantal * bedrag) FROM vertegenwoordigers vw, verkopen vk WHERE vw.vert_nr = vk.vert_nr GROUP BY naam </source>
| Nee, het aantal is al verrekend in de kolom BEDRAG. Dat kun je zien doordat in het voorbeeld de eerste twee rijen hetzelfde product bevatten. In de tweede rij is het aantal 2 en je ziet dat het bedrag daar ook verdubbeld is. | |
|
| Robin Munsterman → Domeinmodellering | Remove this comment when resolved! | |
Is dit niet voldoende? <source lang = 'sql'> SELECT naam, provisie * bedrag FROM vertegenwoordigers vw, verkopen vk WHERE vw.vert_nr = vk.vert_nr </source> Dit zou toch per verkoper de provisie moeten tonen? Aangezien iedere verkoper slechts provisie krijgt over dat wat hij of zij persoonlijk verkocht heeft? Of mis ik hier een belangrijke stap?
| Zo kun je meerdere rijen krijgen omdat in de tabel 'verkopen' meerdere bedragen per vert_nr kunnen voorkomen, omdat per prod_nr het bedrag wordt getoond. Volgens de vraag moet je het totale provisiebedrag per vertegenwoordiger berekenen, dus de bedragen van de verschillende (verkochte) producten van 1 vertegenwoordiger moeten bij elkaar opgeteld worden. Hierdoor komt er een 'aggregated function' in de select en moet er group by gebruikt worden in de query, want min(naam) kan niet omdat je meerdere namen wilt tonen. Maar nu heb ik nog een vraag. Provisie is een gewone kolom en zou normaal gesproken in de group by moeten. Maar nu wordt er een rekensom gedaan met proviesie. Moet provisie dan nog steeds in de group by? |  |
|
| Petra van den Bos → 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 prod_nr, in_voorraad * stukprijs
FROM producten
WHERE prod_nr in (SELECT MAX (in_voorraad * stukprijs)
FROM producten)
of WHERE prod_nr >= ALL (SELECT in_vooraad * stukprijs
FROM producten)
</source>
| Hier gebeurt iets vreemds: een prod_nr wordt vergeleken met een totaalwaarde. Dat lijkt mij iets van appels met koeien vergelijken en kan nooit goed zijn. |  |
|
| Ger Paulussen → Domeinmodellering | Remove this comment when resolved! | |
<source lang='sql'>
SELECT prod_nr, in_voorraad * stukprijs
FROM producten
WHERE in_voorraad * stukprijs IN (SELECT MAX (in_voorraad * stukprijs)
FROM producten)
</source> Klopt de query zo wel?
| {{{3}}} | |
|
| Robin Munsterman → 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 naam
FROM klanten
WHERE klant_nr IN (SELECT count(klant_nr)
FROM verkopen
GROUP BY klant_nr
HAVING count(klant_nr)<3)
</source>
Dit lijkt mij niet goed, wat heeft klant_nr (001, 002 en 003) te maken met count(klant_nr) (bij allemaal 1)? Dus hoe kan klant_nr dan in count(klant_nr) zitten?
| Een zelfde opmerking als bij vraag 4: hoe kun je nu in de WHERE-regel een klant_nt vergelijken met een aantal klanten? Dat zijn toch verschillende grootheden. | |
|
| Ger Paulussen → Domeinmodellering | Remove this comment when resolved! | |
<source lang = 'sql'>
SELECT naam
FROM klanten
WHERE klant_nr IN (SELECT klant_nr
FROM verkopen
GROUP BY klant_nr
HAVING count(klant_nr)<3)
</source> Klopt de query zo wel?
| volgens mij is deze query goed. ik had in ieder geval het zelfde antwoord | ||
| Marvin Barron → Domeinmodellering | Remove this comment when resolved! | |
| De nieuwe uitwerkingen bij vraag 4 en 5 zien er nu gezond uit. In beide gevallen is het ook mogelijk om een join-constructie te gebruiken. Probeer maar eens... | |
|
| Ger Paulussen → Domeinmodellering | Remove this comment when resolved! | |
<source lang = 'sql'> SELECT naam FROM klanten a, verkopen b WHERE a.klant_nr = b.klant_nr HAVING COUNT(*) < 3 GROUP BY klant_nr </source>
| Ik moet toegeven, dat het idee van de join inderdaad in deze query zit, maar er staan hier enkele (!) fouten in die volgens de voorbeelden van 'Some queries with errors' (zie Blackboard) jullie beslist niet zouden mogen maken! | |
|
| Ger Paulussen → Domeinmodellering | Remove this comment when resolved! | |
| {{{3}}} | |
|
| Robin Munsterman → Domeinmodellering | Remove this comment when resolved! | |
| In klanten komt klant_nr maar 1 keer voor, maar in verkopen kan hetzelfde klant_nr meerdere keren voorkomen, dus je mag ze niet aan elkaar gelijk stellen. Dit zie je ook terug in de query met subquery-constructie, want er wordt gebruik gemaakt van IN. Ik zou alleen niet weten hoe je dan wel een join-constructie kunt maken... | |
|
| Petra van den Bos → Domeinmodellering | Remove this comment when resolved! | |
| Jammer voor Petra, maar ik ben het wel met voorgaande query en dus niet zo met Petra eens. Als je op de presidentiële database een join doet van de president- en de pres_hobby-tabel, dan heb je toch ook zo'n situatie als Petra hier beschrijft van maar 1 regel in de president-tabel die gekoppeld wordt aan vaak meerdere regels in de pres_hobby-tabel. Je kunt dan gerust op bijv. P.pres_name groeperen en daarna als HAVING conditie opleggen, dat zo'n president maar maximaal 3 hobbies mag hebben. | |
|
| Ger Paulussen → 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'> SELECT Vert_nr, Naam, Geslacht, Provisie, Salaris FROM VERTEGENWOORDIGERS V1 WHERE Salaris = (SELECT MAX(Salaris) FROM VERTEGENWOORDIGERS V2 WHERE V1.Vert_nr = V2.Vert_nr AND Geslacht = 'V') OR Salaris = (SELECT MAX(Salaris) FROM VERTEGENWOORDIGERS V3 WHERE V1.Vert_nr = V3. Vert_nr AND Geslacht = 'M') </source>
| Ik ben benieuwd of dit mogelijk is. Hetgeen wat ik afvraag is of er een UNION-operator gebruikt moet worden omdat hier (wellicht) V1 is V2 is V3 uitkomt en degene dan tegelijkertijd een man en een vrouw moet zijn. Ook vraag ik mij af of dit niet heel omslachtig is en je bijvoorbeeld ook DISTINCT(Geslacht) kan gebruiken zodat je dit maar één keer hoeft te doen. Bij nader inzien kun je DISTINCT(Geslacht) toch niet gebruiken omdat hij dan alleen degene selecteert met het hoogste salaris. |  |
|
| Bas Elbers → Domeinmodellering | Remove this comment when resolved! | |
| Dit is een erg gecompliceerde en bovendien foutieve oplossing. Bekijk eerst eens goed de besproken voorbeelden over correlated subqueries en vraag je dan af waarom hier in de subquery achter WHERE een relatie zou moeten worden gelegd met het vert_nr uit de hoofdquery. Een andere (cor)relatie is nodig!
P.S. en als je dat duidelijk is, kom je waarschijnlijk ook tot de conclusie dat dat OR-gedeelte niet hoeft... |
|
|
| Ger Paulussen → Domeinmodellering | Remove this comment when resolved! | |
| Moet er hier nu naast de correlated subquery ook nog een group by gebruikt worden? |  |
|
| Koen van Ingen → Domeinmodellering | Remove this comment when resolved! | |
| Hallo Koen, ik kan natuurlijk wel met een simpel 'ja' of 'neen' antwoorden, maar belangrijker lijkt me na te gaan waarom je deze vraag stelt. Heb je voorbeelden gezien met een min of meer vergelijkbare gegevensvraag en zijn daarin wel of niet (en waarom!) group by-constructies gebruikt? | |
|
| Ger Paulussen → Domeinmodellering | Remove this comment when resolved! | |
| {{{3}}} | |
|
| Robin Munsterman → Domeinmodellering | Remove this comment when resolved! | |
| Moet er achter GESLACHT IS V.GESLACHT niet nog een GROUP BY GESLACHT omdat je per geslacht moet geven? |  |
|
| Maarten Bovy → Domeinmodellering | Remove this comment when resolved! | |
| Oja, ik zie het al. Volgens mij moet er dus wel gegroeppeerd worden op geslacht. Dus het moet hier wel. De reden dat ik twijfel is dat het dit keer niet uit te testen is en het dus op papier moest. | |
|
| Koen van Ingen → Domeinmodellering | Remove this comment when resolved! | |
| Jammer voor Maarten en Koen, maar ik ben het [bijna] helemaal met voorgaande query van Robin eens. Als je daar [na het bekijken van alle voorbeelden die we over correlated query hebben besproken] daar nog aan twijfelt, dan het volgende. Wat er in feite gebeurt, is dat de hoofdquery regel voor regel van de gehele tabel afloopt om te kijken of de inhoud van die regel wel getoond moet worden. Uit de formulering van de WHERE-clause kun je opmaken dat een bepaalde regel [kortom van een bepaalde vertegenwoordiger] alleen maar getoond mag worden als het salaris in die regel gelijk is aan -en dan komt de subquery- het maximum van de salarissen van alle vertegenwoordigers van hetzelfde geslacht als dat van de vertegenwoordiger van de beschouwde regel in de hoofdquery. Er worden door de hoofdquery dus maar 2 regels getoond: eentje waarbij een man als salaris het maximum van alle mannen heeft en eentje waarbij een vrouw het maximumsalaris van alle vrouwen heeft (oh sorry, er zouden natuurlijk een paar mannen resp. vrouwen met hetzelfde maximale salaris kunnen zijn). Ik gaf hiervoor aan, dat ik het 'bijna' helemaal met Robin's query eens ben. Het enige dat ik anders zou doen is om in plaats van diens IN-operator het is-gelijk-teken te gebruiken. |
|
|
| Ger Paulussen → Domeinmodellering | Remove this comment when resolved! | |
Vraag 7
7. Van welke bestaande producten zijn nog geen verkopen geregistreerd en is het gewicht méér dan 10 kg?
Antwoord:
| {{{3}}} | |
|
| Robin Munsterman → Domeinmodellering | Remove this comment when resolved! | |
| Moet er niet ook nog een conditie bij in de trant van AND GEWICHT IS NOT NULL? | ||
| Maarten Bovy → Domeinmodellering | Remove this comment when resolved! | |
| Als ik me het goed herinner kom rijen met een nullwaarde voor gewicht niet voor omdat bij de ongelijkheid GEWICHT > 10 je niet kunt zeggen waar NULL zit, en dus worden ze weggelaten. Ik had de query van de sheets om een ranking op te stellen zonder de WHERE clause geprobeerd en toen stonden de rijen met NULL opeens op 1. | ||
| Rob ten Berge → Domeinmodellering | Remove this comment when resolved! | |
Vraag 8
8. Toon de gegevens van de vertegenwoordigers die in totaal meer dan 5 verschillende producten aan klanten hebben verkocht.
Antwoord:
| {{{3}}} | |
|
| Robin Munsterman → Domeinmodellering | Remove this comment when resolved! | |
Heey robin, ik geloof niet dat je een COUNT mag zetten in de WHERE clausule, je kan het denk beter oplossen met een GROUP BY. |
 |
|
| Nicky van Rijsbergen → Domeinmodellering | Remove this comment when resolved! | |
| Ik ben het met Nicky eens; de hier getoonde constructie valt onder de verboden constructies zoals besproken bij de sheets over 'Some queries with errors' (zie Blackboard). | |
|
| Ger Paulussen → 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:
| {{{3}}} | |
|
| Petra van den Bos → Domeinmodellering | Remove this comment when resolved! | |
| {{{3}}} | |
|
| Ger Paulussen → Domeinmodellering | Remove this comment when resolved! | |
| {{{3}}} | |
|
| Maarten Bovy → Domeinmodellering | Remove this comment when resolved! | |
| Hallo Maarten, je zit een heel eind in de goede richting. Twee knelpunten: 1) het gaat om totaalbedragen per plaats; je hoofdquery doet dat correct, maar in de subquery gaat het plotseling over totaalbedrag per klant ... en 2) in de HAVING-regel staat weer zo'n fout die je niet zou mogen maken (zie de 'Some queries with errors' op Blackboard): je vergelijkt een "SUM(bedrag)" met de combinatie van "SUM(bedrag), klant_nr". | |
|
| Ger Paulussen → 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:
| {{{3}}} | ||
| Elmar Dongelmans → Domeinmodellering | Remove this comment when resolved! | |
| Hallo Elmar, een snelle reactie [daarna moet ik richting toets...]: ik heb niet de tijd om nu heel precies van alle correlaties die je maakt precies na te gaan hoe hun uitwerking is, en daarom heel kort een paar dingen die me al opvallen: - het GROUP BY in de hoofdquery lijkt me geheel overbodig en bovendien mag je daar in de HAVING géén Klant_nr gebruiken (dat is geen groepseigenschap [die bijna altijd met statistische functies wordt aangegeven]; zie sheets over 'Some queries with errors' op Blackboard); - als je in die hoofdquery het GROUP BY weglaat en het HAVING door WHERE (het gaat over een regel-conditie) vervangt, dan ziet de hoofdquery er gezond uit. Meestal gebruik je in een HAVING ... > -is-gelijk-teken- ALL constructie vergelijkbare constructen in de eerste [hier de middelste] en de laatste [sub]query. Vanwege al die correlaties kan ik nu niet zo snel zien of je daar in dit geval terecht van kunt afwijken. | |
|
| Ger Paulussen → Domeinmodellering | Remove this comment when resolved! | |
