karin's ::weblog:: |
Montag, 7. April 2008
::Aufgabenstellung 3 SQL::
Karin.Strasser.Uni-Linz, 13:51h
Ausgehend von folgender Abfrage
SELECT Kunde.Nr, Kunde.Nachname, COUNT(Auftrag.Nr) FROM Kunde, Auftrag WHERE Kunde.Nr = Auftrag.Kundennummer GROUP BY Kunde.Nr ORDER BY Kunde.Nr habe ich schrittweise eine Auswertung entwickelt, die hoffentlich betriebswirtschaftlich wertvolle Aussagen produziert. Meine Lösungen: Teilschritt 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: SELECT Kunde.Nr, Kunde.Nachname, COUNT(DISTINCT Auftrag.Nr) AS "Anzahl der Aufträge", SUM(Buch.Preis*Auftragspos.Menge) AS Umsatz 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 ---> durch diesen Befehl erhalte ich also den Umsatz jedes Kunden. Durch die Erweiterung DISTINCT erreiche ich das die Anzahl der Aufträge unverändert bleibt. Wenn ich nun wissen will welcher Kunde den stärksten Umsatz hat setzte ich anstelle von ORDER BY Kunde.Nr ---> ORDER BY Umsatz DESC ein. Somit komme ich zu dem Schluss das der Kunde ERDL mit der Kundennr. 14 und 4 Aufträgen den stärksten Umsatz von 118018.9 aufweist. Teilschritt 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: SELECT Kunde.Nr, Kunde.Nachname, Auftrag.Datum FROM Kunde, Auftrag WHERE Kunde.Nr = Auftrag.Kundennummer GROUP BY Kunde.Nr ORDER BY Kunde.Nr Man kann hier natürlich auch nach anderen Kriterien ordnen (z.b: Auftragsdatum: ORDER BY Auftrag.Datum) Teilschritt 3) Grenzen Sie die Abfrage 1 in sinnvolle Zeitabschnitte ein: Die Aufträge reichen vom 1. Jänner 2000 bis zum 17. Jänner 2001. Meine Berechnung führe ich im Jahr 2000 durch indem ich das Jahr in 2 Jahreshälften einteile: Mein Befehl ändert sich dahin gehend das ich bei SELECT den Schritt Auftrag.Datum einfüge und bei der Bedingung WHERE die Zeitspanne erweitere (1.Halbjahr: 1.1.00-31.6.00): SELECT Kunde.Nr, Kunde.Nachname, COUNT(DISTINCT Auftrag.Nr) AS "Anzahl der Aufträge", SUM(Buch.Preis*Auftragspos.Menge) AS Umsatz, 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 >= "2000-01-01" AND Auftrag.Datum <= "2000-06-31" GROUP BY Kunde.Nr ORDER BY Auftrag.Datum Somit bekomme ich ein Ergebnis das sich über alle Kunden im Zeitraum zw. 1.1.2000-31.6.2000 erstreckt. Das 2.te Halbjahr 2000 wird folgendermaßen berechnet: SELECT Kunde.Nr, Kunde.Nachname, COUNT(DISTINCT Auftrag.Nr) AS "Anzahl der Aufträge", SUM(Buch.Preis*Auftragspos.Menge) AS Umsatz, 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 >= "2000-07-01" AND Auftrag.Datum <= "2000-12-31" GROUP BY Kunde.Nr ORDER BY Auftrag.Datum Somit erhalte ich alle Kunden im Zeitraum 1.7.2000-31.12.2000. Teilschritt 4) Ergänzen Sie die zeitlich abgegrenzten Auswertungen um einen weiteren Filter, der sich an der durchschnittlichen Anzahl der Aufträge orientiert: Ich erweitere die Abfrage um den Filter AVG(Auftragspos.Nr) AS "Durchschnittl. Anzahl der Aufträge" und wähle als Datum das 1.Halbjahr(1.1.2001-31.6.2001) aus: SELECT Kunde.Nr, Kunde.Nachname, COUNT(DISTINCT Auftrag.Nr) AS "Anzahl der Aufträge", SUM(Buch.Preis*Auftragspos.Menge) AS Umsatz, Auftrag.Datum, AVG(Auftragspos.Nr) AS "Durchschnittl. Anzahl der Aufträge" FROM Kunde, Auftrag, Auftragspos, Buch WHERE Kunde.Nr = Auftrag.Kundennummer AND Auftrag.Nr = Auftragspos.Auftragsnummer AND Auftragspos.Buchnummer = Buch.Nr AND Auftrag.Datum >= "2001-01-01" AND Auftrag.Datum <= "2001-06-31" GROUP BY Kunde.Nr ORDER BY Kunde.Nr ------------------------------------------------- Nach einigen zahlreichen Überlegungen aufgrund von anderen Kommentaren und Meinungen meiner Kollegen hab ich nun den Teilschritt 4 noch mal überarbeitet und bin zu folgender Lösung gekommen: SELECT Kunde.Nr, Kunde.Nachname, COUNT(DISTINCT Auftrag.Nr) AS "Anzahl der Aufträge", SUM(Buch.Preis*Auftragspos.Menge) AS Umsatz, Auftrag.Datum, AVG(Auftragspos.Menge) AS "Durchschnittl. Anzahl der Aufträge" FROM Kunde, Auftrag, Auftragspos, Buch WHERE Kunde.Nr = Auftrag.Kundennummer AND Auftrag.Nr = Auftragspos.Auftragsnummer AND Auftragspos.Buchnummer = Buch.Nr AND Auftrag.Datum >= "2000-01-01" AND Auftrag.Datum <= "2000-06-31" GROUP BY Kunde.Nr ORDER BY Kunde.Nr Um die durchschnittliche Anzahl an Aufträgen für das 1.Halbjahr(1.1.2000-31.6.2000) zu berechnen habe ich nun den Filter AVG(Auftragspos.Menge) AS "Durchschnittl. Anzahl der Aufträge" eingefügt und nicht wie zuvor AVG(Auftragspos.Nr)AS "Durchschnittl. Anzahl der Aufträge". ... comment
bjoern.hagemann.Uni-Linz,
Montag, 7. April 2008, 17:46
Siehe Kommentar bei Georg.Pollhammer
... link ... comment |
Online for 6141 days
Last update: 2010.01.19, 10:19 status
You're not logged in ... login
menu
search
calendar
recent updates
B&I Aufgabe 4: Social...
Etsy.com Geschäft by Karin.Strasser.Uni-Linz (2010.01.19, 10:19) B&I Aufgabe 1
Follow the Free - Verschenken von Teilprodukten und... by Karin.Strasser.Uni-Linz (2009.11.09, 15:38) Aufgabe 5
Zusammenfassung unseres Schulbuchhandels Analyse: by Karin.Strasser.Uni-Linz (2009.01.20, 16:49) Aufgabe 4
Ich habe bei der 4. Aufgabe, || Angewandte Kollaboration... by Karin.Strasser.Uni-Linz (2008.12.11, 17:25) ONIX
Hallo Karin. Ich bin beim recherchieren auch auf ONIX... by Anto.Grgic.Uni-Linz (2008.11.22, 20:47) |