Montag, 7. April 2008
Betriebswirtschaftliche Auswertung einer SQL-Abfrage (3. Hausübung aus IV 2)
Astrid.Haider.Uni-Linz, 18:55h
Aufgabe:
Ausgehend von folgender Abfrage auf SQL.IDV.EDU
SELECT Kunde.Nr, Kunde.Nachname, COUNT(Auftrag.Nr)
FROM Kunde, Auftrag
WHERE Kunde.Nr = Auftrag.Kundennummer
GROUP BY Kunde.Nr
ORDER BY Kunde.Nr
sollten Sie schrittweise eine Auswertung entwicklen, die betriebswirtschaftlich wertvolle Aussagen produziert. Gehen Sie wie folgt vor:
1) Ergänzen sie obenstehende Abfrage um den Umsatz (Preis * Menge) je Kunde. Achten Sie darauf, dass die Anzahl der getätigten Aufträge "COUNT(Auftrag.Nr)" korrekt ist, bzw. im Vergleich zur Ausgangssituation unverändert.
2) Ermitteln Sie in einem Weiteren Schritt die Verteilung der Aufträge über die Zeit (Jahre), OHNE dass dabei Auswertungen (Anzahl der Aufträge bzw. Auftragssummen) ermittelt werden.
3) Grenzen Sie die Abfrage 1 in sinnvolle Zeitabschnitte ein.
4) Ergänzen Sie die zeitlich abgegrenzten Auswertungen um einen weiteren Filter, der sich an der durchschnittlichen Anzahl der Aufträge orientiert.
Dokumentieren Sie alle, für die Lösung der Aufgabe durchgeführten Abfragen in Ihrem Weblog und beschreiben Sie den Zweck jeder durchgeführten Abfrage.
Lösung:
Zuerst habe ich mir einmal das Ergebnis der oben angeführten Ausgangsabfrage angesehen:
zu 1)
Um den Umsatz je Kunde abzubilden, habe ich diese Abfrage zuerst folgenderweise ergänzt:
SELECT Kunde.Nr, Kunde.Nachname, COUNT(Auftrag.Nr) AS "Auftragssumme", SUM(Auftragspos.Menge*Buch.Preis) AS "Kundenumsatz" FROM Kunde, Auftrag,Auftragspos, Buch WHERE Kunde.Nr = Auftrag.Kundennummer AND Auftrag.Nr=Auftragspos.Auftragsnummer AND Auftragspos.Buchnummer=Buch.Nr GROUP BY Kunde.Nr ORDER BY Kunde.Nr
Außerdem habe ich die Tabellenspalten umbenannt, in "Auftragssumme" und "Kundenumsatz".
Diese Abfrage liefert aber im Vergleich mit der Ausgangstabelle falsche Auftragssummen!
Daher habe ich es mit folgender Abfrage versucht:
SELECT Kunde.Nr, Kunde.Nachname, COUNT(DISTINCT Auftrag.Nr) AS "Auftragssumme", SUM(Auftragspos.Menge*Buch.Preis) AS "Kundenumsatz" FROM Kunde, Auftrag, Auftragspos, Buch WHERE Kunde.Nr = Auftrag.Kundennummer AND Auftrag.Nr=Auftragspos.Auftragsnummer AND Auftragspos.Buchnummer=Buch.Nr GROUP BY Kunde.Nr ORDER BY Kunde.Nr
In der COUNT-Funktion habe ich deshalb das DISTINCT in die Klammer eingefügt, da ansonsten die Zahl der getätigten Aufträge pro Kunde nicht mit der in der Ausgangstabelle übereingestimmt hätte.
"COUNT und DISTINCT können zusammen in einer Anweisung verwendet werden, um die Anzahl der individuellen Einträge in einer Tabelle auszulesen" ( http://sql.1keydata.com/de/sql-count.php ).
Und siehe da, die Auftragssummen stimmen mit denen der Ausgangstabelle überein. Die Ergebnistabelle sieht nun folgendermaßen aus:
Da mir die Beträge der Kundenumsätze etwas hoch vorgekommen sind, habe ich diese mit folgender Abfrage für die Kundin Brückner (Kunde.Nr=1) kontrolliert:
SELECT a.Auftragsnummer, a.Buchnummer, a.Menge, b.Preis, a.Menge*b.Preis From Kunde, Auftrag, Auftragspos a, Buch b WHERE Kunde.Nr=1 AND Kunde.Nr=Auftrag.Kundennummer AND Auftrag.Nr=a.Auftragsnummer AND a.Buchnummer=b.Nr
Das Ergebnis sieht folgendermaßen aus:
Hier sieht man in der Spalte Auftragsnummer, dass die Kundin Brückner 2 Aufträge getätigt hat (Auftragsnummern 864 und 955). In den Spalten Buchnummer und Menge sieht man, welche Bücher bei den Aufträgen bestellt wurden und in welchen Mengen. In der letzten Spalte kann man den Umsatz je Buch ablesen. Addiert man diese Umsätze aller Bücher der beiden Aufträge erhält man wirklich die gleiche Summe wie die obrige Abfrage liefert. Die hohen Kundenumsätze dürften demnach also korrekt sein.
zu 2)
Mit folgender Abfrage erhält man das Datum der getätigten Aufträge der einzelnen Kunden aufgelistet:
SELECT Kunde.Nr, Kunde.Nachname, Auftrag.Datum
FROM Kunde, Auftrag
WHERE Kunde.Nr = Auftrag.Kundennummer
ORDER BY Kunde.Nr
Die Ergebnistabelle sieht so aus:
zu 3)
Eine zeitliche Abgrenzung des Bestelldatums kann zum Beispiel dann sinnvoll sein, wenn anlässlich des 10. Unternehmensgeburtstages auf alle Aufträge, die z.B. vom 1.1.2001 bis zum 15.1.2001 getätigt wurden, 10% Rabatt vom Auftragswert gewährt werden. Mit der folgenden Abfrage kann ich alle Kunden ermitteln, die in diesem Zeitraum bestellt haben, außerdem wird mir gleichzeitig ihre Auftragssumme angezeigt.
SELECT Kunde.Nr, Kunde.Nachname, COUNT(DISTINCT Auftrag.Nr) AS "Auftragssumme", SUM(Auftragspos.Menge*Buch.Preis) AS "Kundenumsatz", Auftrag.Datum FROM Kunde, Auftrag, Auftragspos, Buch WHERE Kunde.Nr=Auftrag.Kundennummer AND Auftrag.Nr=Auftragspos.Auftragsnummer AND Auftragspos.Buchnummer=Buch.Nr AND Auftrag.Datum BETWEEN "2000-01-01" AND "2000-01-15" GROUP BY Kunde.Nr ORDER BY Kunde.Nr
Die Ergebnistabelle sieht folgendermaßen aus:
zu 4)
Will man nun nur diesen Kunden einen Rabatt gewähren, die mindestens 2 Aufträge getätigt haben, muss man die Abfrage aus 3) zu folgender ergänzen:
SELECT Kunde.Nr, Kunde.Nachname, COUNT(DISTINCT Auftrag.Nr) AS "Auftragssumme", SUM(Auftragspos.Menge*Buch.Preis) AS "Kundenumsatz", Auftrag.Datum
FROM Kunde, Auftrag, Auftragspos, Buch
WHERE Kunde.Nr=Auftrag.Kundennummer AND Auftrag.Nr=Auftragspos.Auftragsnummer AND Auftragspos.Buchnummer=Buch.Nr AND Auftrag.Datum BETWEEN "2000-01-10" AND "2000-01-15"
GROUP BY Kunde.Nr
HAVING COUNT(DISTINCT Auftrag.Nr)>=2
ORDER BY Kunde.Nr
Man erhält folgende Ergebnistabelle:
Ausgehend von folgender Abfrage auf SQL.IDV.EDU
SELECT Kunde.Nr, Kunde.Nachname, COUNT(Auftrag.Nr)
FROM Kunde, Auftrag
WHERE Kunde.Nr = Auftrag.Kundennummer
GROUP BY Kunde.Nr
ORDER BY Kunde.Nr
sollten Sie schrittweise eine Auswertung entwicklen, die betriebswirtschaftlich wertvolle Aussagen produziert. Gehen Sie wie folgt vor:
1) Ergänzen sie obenstehende Abfrage um den Umsatz (Preis * Menge) je Kunde. Achten Sie darauf, dass die Anzahl der getätigten Aufträge "COUNT(Auftrag.Nr)" korrekt ist, bzw. im Vergleich zur Ausgangssituation unverändert.
2) Ermitteln Sie in einem Weiteren Schritt die Verteilung der Aufträge über die Zeit (Jahre), OHNE dass dabei Auswertungen (Anzahl der Aufträge bzw. Auftragssummen) ermittelt werden.
3) Grenzen Sie die Abfrage 1 in sinnvolle Zeitabschnitte ein.
4) Ergänzen Sie die zeitlich abgegrenzten Auswertungen um einen weiteren Filter, der sich an der durchschnittlichen Anzahl der Aufträge orientiert.
Dokumentieren Sie alle, für die Lösung der Aufgabe durchgeführten Abfragen in Ihrem Weblog und beschreiben Sie den Zweck jeder durchgeführten Abfrage.
Lösung:
Zuerst habe ich mir einmal das Ergebnis der oben angeführten Ausgangsabfrage angesehen:
zu 1)
Um den Umsatz je Kunde abzubilden, habe ich diese Abfrage zuerst folgenderweise ergänzt:
SELECT Kunde.Nr, Kunde.Nachname, COUNT(Auftrag.Nr) AS "Auftragssumme", SUM(Auftragspos.Menge*Buch.Preis) AS "Kundenumsatz" FROM Kunde, Auftrag,Auftragspos, Buch WHERE Kunde.Nr = Auftrag.Kundennummer AND Auftrag.Nr=Auftragspos.Auftragsnummer AND Auftragspos.Buchnummer=Buch.Nr GROUP BY Kunde.Nr ORDER BY Kunde.Nr
Außerdem habe ich die Tabellenspalten umbenannt, in "Auftragssumme" und "Kundenumsatz".
Diese Abfrage liefert aber im Vergleich mit der Ausgangstabelle falsche Auftragssummen!
Daher habe ich es mit folgender Abfrage versucht:
SELECT Kunde.Nr, Kunde.Nachname, COUNT(DISTINCT Auftrag.Nr) AS "Auftragssumme", SUM(Auftragspos.Menge*Buch.Preis) AS "Kundenumsatz" FROM Kunde, Auftrag, Auftragspos, Buch WHERE Kunde.Nr = Auftrag.Kundennummer AND Auftrag.Nr=Auftragspos.Auftragsnummer AND Auftragspos.Buchnummer=Buch.Nr GROUP BY Kunde.Nr ORDER BY Kunde.Nr
In der COUNT-Funktion habe ich deshalb das DISTINCT in die Klammer eingefügt, da ansonsten die Zahl der getätigten Aufträge pro Kunde nicht mit der in der Ausgangstabelle übereingestimmt hätte.
"COUNT und DISTINCT können zusammen in einer Anweisung verwendet werden, um die Anzahl der individuellen Einträge in einer Tabelle auszulesen" ( http://sql.1keydata.com/de/sql-count.php ).
Und siehe da, die Auftragssummen stimmen mit denen der Ausgangstabelle überein. Die Ergebnistabelle sieht nun folgendermaßen aus:
Da mir die Beträge der Kundenumsätze etwas hoch vorgekommen sind, habe ich diese mit folgender Abfrage für die Kundin Brückner (Kunde.Nr=1) kontrolliert:
SELECT a.Auftragsnummer, a.Buchnummer, a.Menge, b.Preis, a.Menge*b.Preis From Kunde, Auftrag, Auftragspos a, Buch b WHERE Kunde.Nr=1 AND Kunde.Nr=Auftrag.Kundennummer AND Auftrag.Nr=a.Auftragsnummer AND a.Buchnummer=b.Nr
Das Ergebnis sieht folgendermaßen aus:
Hier sieht man in der Spalte Auftragsnummer, dass die Kundin Brückner 2 Aufträge getätigt hat (Auftragsnummern 864 und 955). In den Spalten Buchnummer und Menge sieht man, welche Bücher bei den Aufträgen bestellt wurden und in welchen Mengen. In der letzten Spalte kann man den Umsatz je Buch ablesen. Addiert man diese Umsätze aller Bücher der beiden Aufträge erhält man wirklich die gleiche Summe wie die obrige Abfrage liefert. Die hohen Kundenumsätze dürften demnach also korrekt sein.
zu 2)
Mit folgender Abfrage erhält man das Datum der getätigten Aufträge der einzelnen Kunden aufgelistet:
SELECT Kunde.Nr, Kunde.Nachname, Auftrag.Datum
FROM Kunde, Auftrag
WHERE Kunde.Nr = Auftrag.Kundennummer
ORDER BY Kunde.Nr
Die Ergebnistabelle sieht so aus:
zu 3)
Eine zeitliche Abgrenzung des Bestelldatums kann zum Beispiel dann sinnvoll sein, wenn anlässlich des 10. Unternehmensgeburtstages auf alle Aufträge, die z.B. vom 1.1.2001 bis zum 15.1.2001 getätigt wurden, 10% Rabatt vom Auftragswert gewährt werden. Mit der folgenden Abfrage kann ich alle Kunden ermitteln, die in diesem Zeitraum bestellt haben, außerdem wird mir gleichzeitig ihre Auftragssumme angezeigt.
SELECT Kunde.Nr, Kunde.Nachname, COUNT(DISTINCT Auftrag.Nr) AS "Auftragssumme", SUM(Auftragspos.Menge*Buch.Preis) AS "Kundenumsatz", Auftrag.Datum FROM Kunde, Auftrag, Auftragspos, Buch WHERE Kunde.Nr=Auftrag.Kundennummer AND Auftrag.Nr=Auftragspos.Auftragsnummer AND Auftragspos.Buchnummer=Buch.Nr AND Auftrag.Datum BETWEEN "2000-01-01" AND "2000-01-15" GROUP BY Kunde.Nr ORDER BY Kunde.Nr
Die Ergebnistabelle sieht folgendermaßen aus:
zu 4)
Will man nun nur diesen Kunden einen Rabatt gewähren, die mindestens 2 Aufträge getätigt haben, muss man die Abfrage aus 3) zu folgender ergänzen:
SELECT Kunde.Nr, Kunde.Nachname, COUNT(DISTINCT Auftrag.Nr) AS "Auftragssumme", SUM(Auftragspos.Menge*Buch.Preis) AS "Kundenumsatz", Auftrag.Datum
FROM Kunde, Auftrag, Auftragspos, Buch
WHERE Kunde.Nr=Auftrag.Kundennummer AND Auftrag.Nr=Auftragspos.Auftragsnummer AND Auftragspos.Buchnummer=Buch.Nr AND Auftrag.Datum BETWEEN "2000-01-10" AND "2000-01-15"
GROUP BY Kunde.Nr
HAVING COUNT(DISTINCT Auftrag.Nr)>=2
ORDER BY Kunde.Nr
Man erhält folgende Ergebnistabelle:
... comment