Montag, 7. April 2008
3. Aufgabenstellung: Weitere SQL-Abfragen
Topic: 'Aufgabenstellungen IV 2'
Diese Aufgabenstellung beschäftigt sich wieder mit der Lehrdatenbank des Buchhandels. Ausgehend von dieser 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
soll schrittweise eine Auswertung erfolgen, die wertvolle betriebswirtschaftliche Aussagen produziert. Folgende Auswertungen wurden hierfür getätigt:
1. Abfrage: Umsatz je Kunde
Mit der folgenden Abfrage möchte man herausfinden wie hoch der Umsatz jedes Kunden ist.
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
Wie aus der Abfrage ersichtlich ist wurde der Befehl COUNT(DISTINCT Auftrag.Nr) eingeführt und nicht lediglich COUNT(Auftrag.Nr) zum Zählen der Aufträge verwendet. Der Grund hierfür ist, dass der Befehl COUNT(Auftrag.Nr) die Anzahl der Datensätze, in denen das Feld Auftrag.Nr einen Wert enthält, zurückgeben würde. Das bedeutet folglich, dass somit diese Zahl ohne den Befehl DISTINCT um einiges zunehmen muss. Führt man hingegen den Befehl COUNT(DISTINCT Auftrag.Nr) durch bzw. ein so entsteht das genannte Problem nicht und die Anzahl der Aufträge bleibt unverändert wie in der Ausgangssituation.
Ergebnis: Ersetzt man weiters den Befehl ORDER BY Kunde.Nr durch ORDER BY 4 DESC so wird ersichtlich, dass Kunde Erdl mit vier Aufträgen der umsatzstärkste Kunde (118.018,90) und Kunde Leupold mit einem Auftrag der umsatzschwächste Kunde (56,78) ist.
2. Abfrage: Verteilung der Aufträge über die Zeit
In einem weiteren Schritt soll die Verteilung der Aufträge über die Zeit angezeigt werden ohne die Anzahl der Aufträge bzw. die Auftragssummen zu ermitteln.
SELECT Kunde.Nr, Kunde.Nachname, Auftrag.Datum
FROM Kunde, Auftrag
WHERE Kunde.Nr = Auftrag.Kundennummer
GROUP BY Kunde.Nr
ORDER BY Kunde.Nr
Die angeführte Abfrage zeigt die Verteilung der Aufträge über die Zeit hinweg geordnet nach der Kundennummer. Möchte man das Ganze nach dem Datum sortieren um zu sehen wie sich diese Verteilung chronologisch entwickelt hat, so muss man beim Befehl ORDER BY anstelle von Kunde.Nr die Zahl 3 hinschreiben um somit nach der dritten Spalte, nämlich dem Datum, zu sortieren. Wird dieser Befehl so stehen gelassen, so wird aufsteigend sortiert; mit dem Zusatzbefehl DESC kann zeitlich absteigend sortiert werden.
Ergebnis: Den aktuellsten Kunden stellt Hilgers dar (Datum: 2001-01-17) und der am wenigsten aktuellste Kunde lautet Steinmans (2000-01-03).
Möchte man hingegen bei der Verteilung über die Zeit lediglich die Jahre anzeigen, so kann man sich folgender SQL-Abfrage bedienen:
SELECT Kunde.Nr, Kunde.Nachname, Year(Auftrag.Datum)
FROM Kunde, Auftrag
WHERE Kunde.Nr = Auftrag.Kundennummer
GROUP BY Kunde.Nr
ORDER BY Kunde.Nr
3. Abfrage: Eingrenzen der 1. Abfrage in Zeitabschnitte
Die Aufgabenstellung lautet, dass die 1. Abfrage in sinnvolle Zeitabschnitte eingegrenzt werden soll. Aus betriebswirtschaftlicher Sicht wäre es vielleicht interessant zu sehen, welche Entwicklung die Kunden im vierteljährlichen Gesichtspunkt gemacht haben. Da die Aufträge vom 1. Jänner 2000 bis zum 17. Jänner 2001 reichen werden im Jahr 2000 vier Abfragen und im Jahr 2001 eine Abfrage nötig sein.
Der Form halber soll hier nur eine Abfrage exemplarisch für die anderen vier Abfragen dargestellt werden, da diese aus Platzgründen zu umfangreich wären und sich immer nur ein Befehl ändert. Dieser sich vierteljährlich verändernde Befehl wird natürlich kurz dargestellt.
Die folgende Abfrage zeigt mir alle Kunden (chronologisch aufsteigend sortiert) die sich im Zeitraum 1.1.2000 bis 31.3.2000 befinden.
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-03-31"
GROUP BY Kunde.Nr
ORDER BY 5
Wird das Datum nun mit dem nachstehenden Befehl geändert, so erhält man alle Kunden (chronologisch aufsteigend sortiert) die sich im Zeitraum 1.4.2000 bis 30.6.2000 befinden.
Auftrag.Datum >= "2000-04-01" AND Auftrag.Datum <= "2000-06-30"
(Alle übrigen Paramater bleiben gleich)
Wird das Datum mit dem nachstehenden Befehl geändert, so erhält man alle Kunden (chronologisch aufsteigend sortiert) die sich im Zeitraum 1.7.2000 bis 30.9.2000 befinden.
Auftrag.Datum >= "2000-07-01" AND Auftrag.Datum <= "2000-09-30"
(Alle übrigen Paramater bleiben gleich)
Wird das Datum mit dem nachstehenden Befehl geändert, so erhält man alle Kunden (chronologisch aufsteigend sortiert) die sich im Zeitraum 1.10.2000 bis 31.12.2000 befinden.
Auftrag.Datum >= "2000-10-01" AND Auftrag.Datum <= "2000-12-31"
(Alle übrigen Paramater bleiben gleich)
Wird das Datum mit dem nachstehenden Befehl geändert, so erhält man alle Kunden (chronologisch aufsteigend sortiert) die sich im Zeitraum 1.1.2001 bis 31.3.2001 befinden.
Auftrag.Datum >= "2001-01-01" AND Auftrag.Datum <= "2001-03-31"
(Alle übrigen Paramater bleiben gleich)
4. Abfrage: Ergänzen der 3. Abfrage um einen Filter
In dieser Abfrage sollen nun die zeitlich abgegrenzten Auswertungen um einen weiteren Filter ergänzt werden, der sich an der durchschnittlichen Anzahl der Aufträge orientiert.
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-03-31"
GROUP BY Kunde.Nr
ORDER BY Kunde.Nr
Mit dem eingeführten Filter AVG(Auftragspos.Menge) lässt sich somit die durchschnittliche Anzahl der Aufträge darstellen. Hierbei wurden in dieser Abfrage wiederrum jene Kunden des 1. Quartals 2000 ausgewählt. Die Kunden der übrigen Quartale können durch das Austauschen der Auftrag.Datum und den entsprechenden Datumseingrenzungen (siehe 3. Abfrage) geändert und abgefragt werden.
SELECT Kunde.Nr, Kunde.Nachname, COUNT(Auftrag.Nr)
FROM Kunde, Auftrag
WHERE Kunde.Nr = Auftrag.Kundennummer
GROUP BY Kunde.Nr
ORDER BY Kunde.Nr
soll schrittweise eine Auswertung erfolgen, die wertvolle betriebswirtschaftliche Aussagen produziert. Folgende Auswertungen wurden hierfür getätigt:
1. Abfrage: Umsatz je Kunde
Mit der folgenden Abfrage möchte man herausfinden wie hoch der Umsatz jedes Kunden ist.
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
Wie aus der Abfrage ersichtlich ist wurde der Befehl COUNT(DISTINCT Auftrag.Nr) eingeführt und nicht lediglich COUNT(Auftrag.Nr) zum Zählen der Aufträge verwendet. Der Grund hierfür ist, dass der Befehl COUNT(Auftrag.Nr) die Anzahl der Datensätze, in denen das Feld Auftrag.Nr einen Wert enthält, zurückgeben würde. Das bedeutet folglich, dass somit diese Zahl ohne den Befehl DISTINCT um einiges zunehmen muss. Führt man hingegen den Befehl COUNT(DISTINCT Auftrag.Nr) durch bzw. ein so entsteht das genannte Problem nicht und die Anzahl der Aufträge bleibt unverändert wie in der Ausgangssituation.
Ergebnis: Ersetzt man weiters den Befehl ORDER BY Kunde.Nr durch ORDER BY 4 DESC so wird ersichtlich, dass Kunde Erdl mit vier Aufträgen der umsatzstärkste Kunde (118.018,90) und Kunde Leupold mit einem Auftrag der umsatzschwächste Kunde (56,78) ist.
2. Abfrage: Verteilung der Aufträge über die Zeit
In einem weiteren Schritt soll die Verteilung der Aufträge über die Zeit angezeigt werden ohne die Anzahl der Aufträge bzw. die Auftragssummen zu ermitteln.
SELECT Kunde.Nr, Kunde.Nachname, Auftrag.Datum
FROM Kunde, Auftrag
WHERE Kunde.Nr = Auftrag.Kundennummer
GROUP BY Kunde.Nr
ORDER BY Kunde.Nr
Die angeführte Abfrage zeigt die Verteilung der Aufträge über die Zeit hinweg geordnet nach der Kundennummer. Möchte man das Ganze nach dem Datum sortieren um zu sehen wie sich diese Verteilung chronologisch entwickelt hat, so muss man beim Befehl ORDER BY anstelle von Kunde.Nr die Zahl 3 hinschreiben um somit nach der dritten Spalte, nämlich dem Datum, zu sortieren. Wird dieser Befehl so stehen gelassen, so wird aufsteigend sortiert; mit dem Zusatzbefehl DESC kann zeitlich absteigend sortiert werden.
Ergebnis: Den aktuellsten Kunden stellt Hilgers dar (Datum: 2001-01-17) und der am wenigsten aktuellste Kunde lautet Steinmans (2000-01-03).
Möchte man hingegen bei der Verteilung über die Zeit lediglich die Jahre anzeigen, so kann man sich folgender SQL-Abfrage bedienen:
SELECT Kunde.Nr, Kunde.Nachname, Year(Auftrag.Datum)
FROM Kunde, Auftrag
WHERE Kunde.Nr = Auftrag.Kundennummer
GROUP BY Kunde.Nr
ORDER BY Kunde.Nr
3. Abfrage: Eingrenzen der 1. Abfrage in Zeitabschnitte
Die Aufgabenstellung lautet, dass die 1. Abfrage in sinnvolle Zeitabschnitte eingegrenzt werden soll. Aus betriebswirtschaftlicher Sicht wäre es vielleicht interessant zu sehen, welche Entwicklung die Kunden im vierteljährlichen Gesichtspunkt gemacht haben. Da die Aufträge vom 1. Jänner 2000 bis zum 17. Jänner 2001 reichen werden im Jahr 2000 vier Abfragen und im Jahr 2001 eine Abfrage nötig sein.
Der Form halber soll hier nur eine Abfrage exemplarisch für die anderen vier Abfragen dargestellt werden, da diese aus Platzgründen zu umfangreich wären und sich immer nur ein Befehl ändert. Dieser sich vierteljährlich verändernde Befehl wird natürlich kurz dargestellt.
Die folgende Abfrage zeigt mir alle Kunden (chronologisch aufsteigend sortiert) die sich im Zeitraum 1.1.2000 bis 31.3.2000 befinden.
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-03-31"
GROUP BY Kunde.Nr
ORDER BY 5
Wird das Datum nun mit dem nachstehenden Befehl geändert, so erhält man alle Kunden (chronologisch aufsteigend sortiert) die sich im Zeitraum 1.4.2000 bis 30.6.2000 befinden.
Auftrag.Datum >= "2000-04-01" AND Auftrag.Datum <= "2000-06-30"
(Alle übrigen Paramater bleiben gleich)
Wird das Datum mit dem nachstehenden Befehl geändert, so erhält man alle Kunden (chronologisch aufsteigend sortiert) die sich im Zeitraum 1.7.2000 bis 30.9.2000 befinden.
Auftrag.Datum >= "2000-07-01" AND Auftrag.Datum <= "2000-09-30"
(Alle übrigen Paramater bleiben gleich)
Wird das Datum mit dem nachstehenden Befehl geändert, so erhält man alle Kunden (chronologisch aufsteigend sortiert) die sich im Zeitraum 1.10.2000 bis 31.12.2000 befinden.
Auftrag.Datum >= "2000-10-01" AND Auftrag.Datum <= "2000-12-31"
(Alle übrigen Paramater bleiben gleich)
Wird das Datum mit dem nachstehenden Befehl geändert, so erhält man alle Kunden (chronologisch aufsteigend sortiert) die sich im Zeitraum 1.1.2001 bis 31.3.2001 befinden.
Auftrag.Datum >= "2001-01-01" AND Auftrag.Datum <= "2001-03-31"
(Alle übrigen Paramater bleiben gleich)
4. Abfrage: Ergänzen der 3. Abfrage um einen Filter
In dieser Abfrage sollen nun die zeitlich abgegrenzten Auswertungen um einen weiteren Filter ergänzt werden, der sich an der durchschnittlichen Anzahl der Aufträge orientiert.
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-03-31"
GROUP BY Kunde.Nr
ORDER BY Kunde.Nr
Mit dem eingeführten Filter AVG(Auftragspos.Menge) lässt sich somit die durchschnittliche Anzahl der Aufträge darstellen. Hierbei wurden in dieser Abfrage wiederrum jene Kunden des 1. Quartals 2000 ausgewählt. Die Kunden der übrigen Quartale können durch das Austauschen der Auftrag.Datum und den entsprechenden Datumseingrenzungen (siehe 3. Abfrage) geändert und abgefragt werden.
bjoern.hagemann.Uni-Linz,
07. Apr 08
Korrigiert mich, wenn ich mich irre aber:
da ich nun deine "Lösungen" in mind. einem anderen Weblog gesehen habe möchte ich doch auf eines hinweisen:
Das AVG(Auftragspos.Nr) liefert absolut krumme Werte, die zu jedem Kunden gebündelt werden, da die Aufträge nach Kunden gruppiert werden. Es werden also (auch mehrfach genannte Nummern wie unter dem Disctinct-Punkt erklärt) alle Auftragsnummern aufsummiert (der 10. Auftrag addiert einfach fröhlich eine 10 auf die Zahl) und dann davon der Durchschnitt gebildet. Habe ich etwas übersehen?
Meiner Meinung nach ist dieser Wert nicht aussagekräftig.
da ich nun deine "Lösungen" in mind. einem anderen Weblog gesehen habe möchte ich doch auf eines hinweisen:
Das AVG(Auftragspos.Nr) liefert absolut krumme Werte, die zu jedem Kunden gebündelt werden, da die Aufträge nach Kunden gruppiert werden. Es werden also (auch mehrfach genannte Nummern wie unter dem Disctinct-Punkt erklärt) alle Auftragsnummern aufsummiert (der 10. Auftrag addiert einfach fröhlich eine 10 auf die Zahl) und dann davon der Durchschnitt gebildet. Habe ich etwas übersehen?
Meiner Meinung nach ist dieser Wert nicht aussagekräftig.
Karin.Strasser.Uni-Linz,
07. Apr 08
hi!
vl. kannst du einen tipp geben wie du es machen würdest?
welche korrektur würdest du vorschlagen?
vl. kannst du einen tipp geben wie du es machen würdest?
welche korrektur würdest du vorschlagen?
bjoern.hagemann.Uni-Linz,
07. Apr 08
Ich fand die Aufgabenstellung allgemein recht verwirrend. Ich habe folgendes getan: Jeder Kunde bestellt in einem Bestellvorgang eine unterschiedliche Anzahl von Büchern. Ich lasse nun gebündelt bei jedem Kunden die durchschnittlich bestellte Anzahl an Büchern pro Bestellvorgang anzeigen.
Hat ein Kunde beispielsweise 6 Bestellungen in einem Zeitintervall (Jahr, Monat, Quartal) getätigt und insgesamt 18 Bücher somit erworben, ist das ein Wert von 3 Büchern pro Bestellung.
Erreicht wird dies über AVG(Auftragspos.Menge), da in unsrer Abfrage ja ohnehin alle Aufträge und die damit verknüpften Auftragsposinstanzen pro Kunde gruppiert werden.
Die Probe für einen Kunden kann man in in meinem blog unterSchritt 4 nachlesen.
Hat ein Kunde beispielsweise 6 Bestellungen in einem Zeitintervall (Jahr, Monat, Quartal) getätigt und insgesamt 18 Bücher somit erworben, ist das ein Wert von 3 Büchern pro Bestellung.
Erreicht wird dies über AVG(Auftragspos.Menge), da in unsrer Abfrage ja ohnehin alle Aufträge und die damit verknüpften Auftragsposinstanzen pro Kunde gruppiert werden.
Die Probe für einen Kunden kann man in in meinem blog unterSchritt 4 nachlesen.
Hans.Mittendorfer.Uni-Linz,
07. Apr 08
Zur Klarstellung:
Die Kunden erteilen Aufträge in unterschiedlichen Zeitabständen und in beliebiger Anzahl. Insgesamt wurden 999 Aufträge in der Datenbank erfasst (SELECT COUNT(*) FROM Auftrag)
Jeder Auftrag kann eine individuelle Anzahl von Auftragspositionen enthalten.
Jede Auftragsposition bezieht sich auf ein bestimmtes Buch aus ca. 4.000 unterschiedlichen Büchern, die Anzahl der angebotenen Bücher lässt sich mit der Abfrage SELECT COUNT(*) FROM Buch bestimmen. Jedes Buch wird in einer individuellen Menge bestellt.
Z.B. können die Auftragspositionen des Auftrages mit der Auftragsnummer 67 wie folgt ermittelt werden: SELECT Buchnummer, Menge FROM Auftragspos WHERE Auftragsnummer = 67
Der Auftrag mit der Nummer 67 wurde von Kunde Nr 142 am 25.1.2000 erteilt. SELECT Nr, Datum, Kundennummer FROM Auftrag WHERE Nr = 67
Der Kunde mit der Kundennummer 142 hat 4 Aufträge erteilt: SELECT Nr, Datum, Kundennummer FROM Auftrag WHERE Kundennummer = 142
Die Liste aller bestellten Bücher der 4 Aufträge des Kunden mit der Kundennummer 142 mit den jeweiligen Mengen ist: SELECT Auftragsnummer, Buchnummer, Menge FROM Auftrag, Auftragspos WHERE Auftrag.Nr = Auftragspos.Auftragsnummer AND Kundennummer = 142
Überlegen Sie sich nun, was ein Durchschnittswert aussagt. Syntaktisch wird sich schnell ein Durchschnittswert ermittelt lassen (SQL ist in dieser Hinsicht geduldig). Die Interpretation in Bezug auf eine betriebswirtschaftlich sinnvolle Aufgabenstellung steht auf einem anderen Blatt.
Die Kunden erteilen Aufträge in unterschiedlichen Zeitabständen und in beliebiger Anzahl. Insgesamt wurden 999 Aufträge in der Datenbank erfasst (SELECT COUNT(*) FROM Auftrag)
Jeder Auftrag kann eine individuelle Anzahl von Auftragspositionen enthalten.
Jede Auftragsposition bezieht sich auf ein bestimmtes Buch aus ca. 4.000 unterschiedlichen Büchern, die Anzahl der angebotenen Bücher lässt sich mit der Abfrage SELECT COUNT(*) FROM Buch bestimmen. Jedes Buch wird in einer individuellen Menge bestellt.
Z.B. können die Auftragspositionen des Auftrages mit der Auftragsnummer 67 wie folgt ermittelt werden: SELECT Buchnummer, Menge FROM Auftragspos WHERE Auftragsnummer = 67
Der Auftrag mit der Nummer 67 wurde von Kunde Nr 142 am 25.1.2000 erteilt. SELECT Nr, Datum, Kundennummer FROM Auftrag WHERE Nr = 67
Der Kunde mit der Kundennummer 142 hat 4 Aufträge erteilt: SELECT Nr, Datum, Kundennummer FROM Auftrag WHERE Kundennummer = 142
Die Liste aller bestellten Bücher der 4 Aufträge des Kunden mit der Kundennummer 142 mit den jeweiligen Mengen ist: SELECT Auftragsnummer, Buchnummer, Menge FROM Auftrag, Auftragspos WHERE Auftrag.Nr = Auftragspos.Auftragsnummer AND Kundennummer = 142
Überlegen Sie sich nun, was ein Durchschnittswert aussagt. Syntaktisch wird sich schnell ein Durchschnittswert ermittelt lassen (SQL ist in dieser Hinsicht geduldig). Die Interpretation in Bezug auf eine betriebswirtschaftlich sinnvolle Aufgabenstellung steht auf einem anderen Blatt.
Georg.Pollhammer.Uni-Linz,
07. Apr 08
Vielen Dank an alle für die hilfreichen Antworten. Ich war mir leider bei der vierten Aufgabe selbst nich ganz sicher und wollte diese noch anpassen. Die "richtige" Lösung, welche ich im Kusss-Forum erwähnt hätte und dachte dass diese auch richtig ist, gilt nur für die 1. Aufgabe.
