Anfragen in SQL < Datenbanken < Praktische Inform. < Hochschule < Informatik < Vorhilfe
|
| Status: |
(Frage) beantwortet  | | Datum: | 20:27 Fr 06.02.2009 | | Autor: | Moe007 |
| Aufgabe | Geg. ist folgendes DB-Schema:
Kunde [mm] (\underline{Knr}, [/mm] Name, Vorname)
[mm] Bestellung(\underline{Bnr}, [/mm] Datum)
[mm] Artikel(\underline{Anr}, [/mm] Bezeichnung, KatNr[Kategorie])
[mm] Best_K_A(\underline{Bnr[Bestellung]}, \underline{Anr[Artikel]}, [/mm] Knr[Kunde], Anzahl)
Kategorie [mm] (\underline{KatNr}, [/mm] Bezeichnung)
Aufgabe:
Es dürfen kein TOP(n) oder LIMIT verwendet werden!
a) Es sollen die 5 Kunden mit der größten Anzahl an bestellten Artikeln (Summe) ermittelt werden. Auszugeben sind Vorname, Name und Gesamtanzahl der verkaufeten Artikel sortiert nach Name aufsteigend.
b) Es sollen die 5 am meisten (nicht am häufigsten) bestellten Artikel (Summe der Anzahl) ermittelt werden. Auszugeben sind Artikelbezeichnung und Gesamtzahl sortiert nach Artikelbezeichnung.
Achtung: Untersch. Artikel (untersch. Artikelnummer) können die gleiche Bezeichnung tragen!
|
Hallo!
ich hoffe, es kann mir jemand bei diesen komplexen Anfragen weiter helfen. Ich hab versucht, beide Teilaufgaben zu lösen, bin mir aber überhaupt nicht sicher, ob das so stimmt, wie ich das gemacht habe. Daher würde ich mich, über eine Korrektur freuen, danke!
a) SELECT Vorname, Name, sum(Anzahl)
FROM Kunde NATURAL JOIN [mm] Best_K_A
[/mm]
WHERE Anzahl IN (select max(Anzahl) from [mm] Best_K_A)
[/mm]
GROUP BY Vorname, Name
HAVING count(*) = 5
ORDER BY Name ASC
Glaube zwar nicht, dass das so richtig ist, aber ich weiß nicht besser, wie das geht... Wie macht man das, um auf die 5 Kunden mit der größten Anzahl an best. Artikeln zu gelangen?
b) SELECT A1.Bezeichnung, sum(A1.Anzahl)
FROM Artikel A1 NATURAL JOIN [mm] Best_K_A [/mm] b
GROUP BY A1.Bezeichnung
HAVING count(A1.Bezeichnung) = 5
AND sum(A1.Anzahl) > (SELECT sum(A2.Anzahl) FROM Artikel A2 NATURAL JOIN [mm] Best_K_A [/mm] b)
ORDER BY A1.Bezeichnung ASC
Hier wusste ich nicht genau, wo ich den Hinweis beachten muss.
Ich hoffe, es kann mir jemand die richtigen Anfragen erklären.
Viele Grüße,
Moe
|
|
| |
|
| Status: |
(Antwort) fertig  | | Datum: | 15:51 So 08.02.2009 | | Autor: | rainerS |
Hallo Moe!
> Geg. ist folgendes DB-Schema:
> Kunde [mm](\underline{Knr},[/mm] Name, Vorname)
> [mm]Bestellung(\underline{Bnr},[/mm] Datum)
> [mm]Artikel(\underline{Anr},[/mm] Bezeichnung, KatNr[Kategorie])
> [mm]Best_K_A(\underline{Bnr[Bestellung]}, \underline{Anr[Artikel]},[/mm]
> Knr[Kunde], Anzahl)
> Kategorie [mm](\underline{KatNr},[/mm] Bezeichnung)
>
> Aufgabe:
> Es dürfen kein TOP(n) oder LIMIT verwendet werden!
>
> a) Es sollen die 5 Kunden mit der größten Anzahl an
> bestellten Artikeln (Summe) ermittelt werden. Auszugeben
> sind Vorname, Name und Gesamtanzahl der verkaufeten Artikel
> sortiert nach Name aufsteigend.
> b) Es sollen die 5 am meisten (nicht am häufigsten)
> bestellten Artikel (Summe der Anzahl) ermittelt werden.
> Auszugeben sind Artikelbezeichnung und Gesamtzahl sortiert
> nach Artikelbezeichnung.
> Achtung: Untersch. Artikel (untersch. Artikelnummer)
> können die gleiche Bezeichnung tragen!
>
> Hallo!
> ich hoffe, es kann mir jemand bei diesen komplexen
> Anfragen weiter helfen. Ich hab versucht, beide
> Teilaufgaben zu lösen, bin mir aber überhaupt nicht sicher,
> ob das so stimmt, wie ich das gemacht habe. Daher würde ich
> mich, über eine Korrektur freuen, danke!
>
> a) SELECT Vorname, Name, sum(Anzahl)
> FROM Kunde NATURAL JOIN [mm]Best_K_A[/mm]
> WHERE Anzahl IN (select max(Anzahl) from [mm]Best_K_A)[/mm]
> GROUP BY Vorname, Name
> HAVING count(*) = 5
> ORDER BY Name ASC
>
> Glaube zwar nicht, dass das so richtig ist, aber ich weiß
> nicht besser, wie das geht... Wie macht man das, um auf die
> 5 Kunden mit der größten Anzahl an best. Artikeln zu
> gelangen?
Zu deiner letzten Frage: schau mal ![[]](/images/popup.gif) hier!
Du solltest dir vielleicht eine Beispieldatenbank bauen und deine SQL-Statements ausprobieren. Das, was du hingeschrieben hast, wird höchstwahrscheinlich zu einem Syntaxfehler führen, weil ein Stern in einer HAVING-Clause keinen Sinn ergibt. Die WHERE-Bedingung, die du hinschreibst, wählt den Artikel aus, der am häufigsten in einer Bestellung vorkommt; das ist also falsch. Außerdem ordnest du das Ergebnis nach Name, du musst aber zunächst nach der Gesamtzahl ordnen, und die ersten 5 Zeilen auswählen zu können, also brauchst du dieses sub-SELECT:
SELECT Vorname, Name, sum(Anzahl) AS Gesamtzahl
FROM Kunde NATURAL JOIN Best_K_A
GROUP BY Vorname, Name ORDER BY Gesamtzahl
Für die Methode, die in dem zitierten Artikel genannt wird, brauchst du das dann zweimal.
>
> b) SELECT A1.Bezeichnung, sum(A1.Anzahl)
> FROM Artikel A1 NATURAL JOIN [mm]Best_K_A[/mm] b
> GROUP BY A1.Bezeichnung
> HAVING count(A1.Bezeichnung) = 5
> AND sum(A1.Anzahl) > (SELECT sum(A2.Anzahl) FROM
> Artikel A2 NATURAL JOIN [mm]Best_K_A[/mm] b)
> ORDER BY A1.Bezeichnung ASC
Gleicher Fehler: erst nach Gesamtzahl, also SUM(Anzahl) sortieren
> Hier wusste ich nicht genau, wo ich den Hinweis beachten
> muss.
Ganz einfach: da du die Artikelnummer nicht berücksichtigst, addierst du unterschiedliche Artikel zusammen, wenn die Bezeichnung übereinstimmt.
Also:
SELECT A1.Bezeichnung, A1.Anr, sum(A1.Anzahl) AS Gz
FROM Artikel A1 NATURAL JOIN Best_K_A b
GROUP BY A1.Anr, A1.Bezeichnung
ORDER BY Gz
liefert dir die Liste, deren erste 5 Zeilen du auswählen und nach Bezeichnung sortieren musst.
Viele Grüße
Rainer
|
|
|
| |
|
| Status: |
(Frage) beantwortet | | Datum: | 20:49 So 08.02.2009 | | Autor: | Moe007 |
Hallo Rainer!
vielen Dank für deine Hilfe!
Ich habe nun versucht, deine Tipps anzuwenden und hab nun diese Anweisungen:
a)
SELECT Name, Vorname, sum(Anzahl) AS Gesamtzahl
FROM (SELECT Name, Vorname, sum(Anzahl) AS Gesamtzahl
FROM Kunde NATURAL JOIN [mm] Best_K_A
[/mm]
GROUP BY Name, Vorname
ORDER BY Gesamtzahl)
GROUP BY Name, Vorname
HAVING COUNT(Gesamtzahl)=5
ORDER BY Name
Habe ich das sub-Select richtig eingebaut? Ich habe mir den Link durchgelesen, den du angegeben hast, und da haben sie das auch mit Having count gemacht, um auf die ersten n reihen zu zugreifen. Kann ich das so machen mit HAVING COUNT(Gesamtzahl)=5? Werden da automatisch die ersten 5 ausgesucht?
Dann habe ich am Schluss noch ORDER BY Name, weil in der Angabe das so verlangt war.
b)
SELECT Bezeichnung, sum(Anzahl) AS Gesamtzahl
FROM (SELECT ANR, Bezeichnung, sum(Anzahl) AS Gesamtzahl
FROM Artikel NATURAL JOIN [mm] Best_K_A
[/mm]
GROUP BY Anr, Bezeichnung
ORDER BY Gesamtzahl)
GROUP BY Bezeichnung
HAVING COUNT (Anr) = 5
ORDER BY Bezeichnung
Danke für deine Hilfe.
Viele Grüße,
Moe
|
|
|
| |
|
| Status: |
(Antwort) fertig | | Datum: | 20:20 Di 10.02.2009 | | Autor: | rainerS |
Hallo Moe!
> Hallo Rainer!
>
> vielen Dank für deine Hilfe!
> Ich habe nun versucht, deine Tipps anzuwenden und hab nun
> diese Anweisungen:
>
> a)
> SELECT Name, Vorname, sum(Anzahl) AS Gesamtzahl
> FROM (SELECT Name, Vorname, sum(Anzahl) AS Gesamtzahl
> FROM Kunde NATURAL JOIN [mm]Best_K_A[/mm]
> GROUP BY Name, Vorname
> ORDER BY Gesamtzahl)
> GROUP BY Name, Vorname
> HAVING COUNT(Gesamtzahl)=5
> ORDER BY Name
>
> Habe ich das sub-Select richtig eingebaut? Ich habe mir den
> Link durchgelesen, den du angegeben hast, und da haben sie
> das auch mit Having count gemacht, um auf die ersten n
> reihen zu zugreifen. Kann ich das so machen mit HAVING
> COUNT(Gesamtzahl)=5? Werden da automatisch die ersten 5
> ausgesucht?
Also erst einmal wird das deswege nicht funktionieren, weil du in beiden SELECTs dieselben Namen verwendest. Außerdem hast du übersehen, dass in dem Beispiel ein JOIN der Tabelle mit sich selbst gemacht wurde.
Um das Problem zu vereinfachen, nehmen wir mal an, dass die interessanten Informationen, also Name, Vorname und Gesamtzahl in einer eigenen Tabelle Tab stehen. Dann sieht die Abfrage doch so aus:
SELECT E.Name, E.Vorname, E.Gesamtzahl
FROM Tab AS E, Tab AS B
WHERE E.Gesamtzahl <= B.Gesamtzahl
GROUP BY E.Name, E.Vorname, E.Gesamtzahl
HAVING COUNT(B.Gesamtzahl) <= 5
Du hast also zunächst das Kreuzprodukt der Tabelle mit sich selbst; wobei durch die WHERE-Bedingung alle diejenigen Zeilen wegfallen, in denen E.Gesamtzahl größer als B.Gesamtzahl ist. Dann fasst du mit GROUP BY alle Zeilen zusammen, die die gleichen Werte von E.Name, E.Vorname, E.Gesamtzahl haben und wählst nur diejenigen aus, die 5 oder weniger Elemente haben. Damit wählst du die 5 größten Werte von E.Gesamtzahl aus, denn für kleinere Werte von E.Gesamtzahl gibt es mehr als 5 Einträge von B.Gesamtzahl mit E.Gesamtzahl <= B.Gesamtzahl.
So und jetzt musst du Tab durch geeignete Sub-SELECTs ersetzen und das Ergebnis noch richtig sortieren.
Viele Grüße,
Rainer
|
|
|
|
