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.

Die erste Hausübung aus dem Tutorium vom 1. April 2008 war es, folgende Aufgabenstellungen durchzuführen:

+ Textfeld (einfügen)
+ Diagramm (erstellen)
+ Zellenumrandungen (durchführen)
+ Grafik (einfügen)
+ alles soll auf 1 A4-Seite gemacht werden!

Die folgende Excel-Datei beinhaltet meine Lösung zu den genannten Aufgabenstellungen.

Auf dem Weblog von Patrick Führer findet ihr mein Protokoll des zweiten Tutoriums der LVA Informationsverarbeitung 2.

Bg Georg