"Just in Time", also typisch für mich, gibts hier die Dokumentation zur 2. Hausübung in IV2. Dieses Mal sollte es ja nicht so leicht werden und bereits am Ende der letzten Woche, als die Aufgabe ins Netz gestellt wurde, hab ich mir die Angabe durchgelesen um mir einen ersten Überblick zu verschaffen ohne andererseits schon einen Lösungsversuch zu starten.

Aufgabe war die schrittweise Entwicklung einer komplexen Abfrage mit betriebswirtschaftlichen Sinn. Die Basisabfrage wurde vorgegeben und lautete:

SELECT Kunde.Nr, Kunde.Nachname, COUNT(Auftrag.Nr)
FROM Kunde, Auftrag
WHERE Kunde.Nr = Auftrag.Kundennummer
GROUP BY Kunde.Nr
ORDER BY Kunde.Nr

Man erhält also eine Kundenliste und wieviel Aufträge jeder Kunde bereits durchgeführt hat.

In einem ersten Schritt soll die Abfrage um den Gesamtumsatz pro Kunde erweitert werden. Man würde also zuerst folgende Abfrage versuchen:

SELECT Kunde.Nr, Kunde.Nachname, COUNT(Auftrag.Nr), SUM(Auftragspos.Menge*Buch.Preis)
FROM Kunde, Auftrag, Auftragspos, Buch
WHERE Kunde.Nr = Auftrag.Kundennummer
AND Auftrag.Nr=Auftragspos.Auftragsnummer
AND Buch.Nr=Auftragspos.Buchnummer
GROUP BY Kunde.Nr
ORDER BY Kunde.Nr

Vergleicht man aber jetzt die Gesamtanzahl der Aufträge je Kunde, ergeben sich Differenzen. Jetzt sind mehr Aufträge pro Kunde angegeben, da Aufträge jetzt wohl mit der Anzahl der jeweiligen Bücherliste des Auftrages einfließen.

Somit darf jeder Auftrag trotzdem nur einmal gezählt werden. Dies erreicht man mit der DISTINCT-Klausel bei der COUNT-Funktion. (Anm.: auf die Lösung kamen ein Kollege und ich beim Diskutieren der Hausübung)

SELECT Kunde.Nr, Kunde.Nachname, COUNT(DISTINCT Auftrag.Nr), SUM(Auftragspos.Menge*Buch.Preis)
FROM Kunde, Auftrag, Auftragspos, Buch
WHERE Kunde.Nr = Auftrag.Kundennummer
AND Auftrag.Nr=Auftragspos.Auftragsnummer
AND Buch.Nr=Auftragspos.Buchnummer
GROUP BY Kunde.Nr
ORDER BY Kunde.Nr

Oben sieht man also die Lösung des Problems des ersten Teils.


Im zweiten Teil sollte ermittelt werden, wann denn nun überhaupt Aufträge der verschiedenen Kunden eingetroffen sind. Deswegen sehen wir uns zuerst an, wann unser erster Auftrag und wann unser letzter Auftrag eingegangen ist. (Anm.: Hier gab es Hilfestellungen im Kurs am Mittwoch, den 09.04.08)

Wir stellen also zwei kleine Abfragen:

SELECT MIN(Auftrag.Datum)
FROM Auftrag

SELECT MAX(Auftrag.Datum)
FROM Auftrag

Wir stellen fest: Aufträge gingen vom 01.03.2000 bis 19.01.2001 ein!

Für mich stellte sich auch die Frage, ob es sich bei der Anzahl der Aufträge im Jahr 2001 überhaupt noch um wesentliche Mengen handelte, weswegen ich die Anzahl der Aufträge 2000 und 2001 mit folgenden kleinen Abfragen bestimmte:

SELECT COUNT(Auftrag.Nr)
FROM Auftrag
WHERE Auftrag.Datum >= '2001-01-01'

... was nur 24 Aufträge vom 01.01.2001 bis zum 19.01.2001 ergab (betriebswirtschaftlich noch kein Vergleich möglich)

SELECT COUNT(Auftrag.Nr)
FROM Auftrag
WHERE Auftrag.Datum < '01.01.2001'

Hier waren 975 Aufträge verzeichnet.

Alternativ könnte man hier auch die in der Lehrveranstaltung bezeichnete YEAR-Funktion anwenden, wobei man die Tabelle dann eben noch zusätzlich auswerten muss:

SELECT Kunde.Nr, Kunde.Nachname, YEAR (Datum)
FROM Auftrag, Kunde
WHERE Auftrag.Kundennummer = Kunde.Nr
ORDER BY 3 DESC


Kommen wir also zum 3. Teil der Hausübung:

Die Ergebnisse des ersten Teils sollte nun in geeignete sinnvolle Zeitabschnitte eingeteilt werden, um sie dann vergleichen zu können.

Ich wählte hier Quartale, wobei ich nur die 4 Quartale von 2000 miteinander vergleichen wollte, da das halbe Monat in 2001 für mich als unwesentlich ausschied. Ich erweiterte also lediglich die in der Teilaufgabe 1 entwickelte Abfrage um eine Bedingung:

SELECT Kunde.Nr, Kunde.Nachname, COUNT(DISTINCT Auftrag.Nr) AS 'Anzahl Aufträge', SUM(Auftragspos.Menge*Buch.Preis) AS Gesamtumsatz
FROM Kunde, Auftrag, Auftragspos, Buch
WHERE Kunde.Nr = Auftrag.Kundennummer
AND Auftrag.Nr=Auftragspos.Auftragsnummer
AND Buch.Nr=Auftragspos.Buchnummer
AND Auftrag.Datum BETWEEN '2000-01-01' AND '2000-03-31'
GROUP BY Kunde.Nr
ORDER BY Kunde.Nr

Dies stellt die Gesamtaufzahl der Aufträge pro Kunde mit dem jeweiligen Gesamtumsatz im ersten Quartal dar. Indem man jetzt die Bedingung ändert, erhält man vergleichbare Tabellen der anderen 3 Quartale.


Im vierten und letzten Teil der Hausübung soll nun irgendwie ein Filter bezüglich des Durchschnittes der eingegangenen Aufträge pro Kunde mit einbezogen werden. Überlegt habe ich mir folgendes Szenario:

Der Buchhandel will nachträglich seinen "fleißig" bestellenden Kunden eine Gutschrift als nachträglicher Rabatt zukommen lassen, damit sie auch "fleißige" Kunden bleiben ;). Alle, die überdurchschnittlich viele Aufträge durchgeführt haben sollen 1,5 % ihres Umsatzes/Quartal als Gutschrift erhalten. (Anm.: sinnvoller wäre eventuell eher die Bindung an den Gesamtumsatz!)

Ich brauche also eine weitere Bedingung, die die AVG-Funktion enthält und ich werde das Feld des Gesamtumsatzes gegen ein Feld "Gutschrift" tauschen. Ich schaffte es allerdings nicht, das in eine funktionierende einzelne Abfrage zu packen und so machte ich zwei Abfragen, um den Durchschnitt pro Quartal an Auftragseingängen/Kunde zu ermitteln und dann diese als absolute Zahl in der Bedingung der Hauptabfrage einzufügen.

Die erste Abfrage stellt sich also wie folgt dar:

SELECT COUNT(a.Nr)/COUNT(DISTINCT k.Nr) AS 'Durchschnittliche Aufträge pro Kunde'
FROM Kunde=k, Auftrag=a
WHERE k.Nr=a.Kundennummer
AND a.Datum BETWEEN '2000-01-01' AND '2000-03-31'

Dabei ergab sich eine durchschnittliche Auftragsanzahl/Kunde von 1,3978. Die Gutschrift sollten also alle erhalten, die mehr als einen Auftrag hatten. Das führte mich dann zur finalen Abfrage:


SELECT k.Nr, k.Nachname, COUNT(DISTINCT a.Nr) AS "Anzahl Aufträge", SUM(ap.Menge*b.Preis*0.015) AS Gutschrift
FROM Kunde=k, Auftrag=a, Auftragspos=ap, Buch=b
WHERE k.Nr = a.Kundennummer
AND a.Nr=ap.Auftragsnummer
AND b.Nr=ap.Buchnummer
AND a.Datum BETWEEN '2000-01-01' AND '2000-03-31'
GROUP BY 1
HAVING COUNT(DISTINCT a.Nr) > 1
ORDER BY 1

Bin nun wirklich JIT fertig um 23:59!!

Aufgabe war die Erstellung von 5 selbstgestellten Abfragen an der SQL-Datenbank mit jeweils steigendem Schwierigkeitsgrad.
Dadurch, dass ich in Informationsverarbeitung I kein SQL gemacht habe, da der Schwerpunkt (zugegeben vor 7 Semestern) auf Excel lag, war ich auf das Tutorium angewiesen um die Grundbegriffe zu erlernen. Hier meine ersten komplett selbst erstellten Abfragen:

1. Welche Verlage bieten Bücher an und unter welcher Kurzbezeichnung werden sie geführt?

SELECT v.Name, v.Kurzbezeichnung
FROM Verlag v
ORDER BY 1 ASC

Dies ist die einfachste aller Abfragen, da keine Bedingungen berücksichtigt werden müssen. Als nächstes fügen wir weitere Datenfelder aus anderen Dateien hinzu.

2. Führe alle angebotenen Bücher mit dem Autor an und welche Verlage sie veröffentlichen!

SELECT b.Titel, b.Autor, v.Name
FROM Verlag v, Buch b
WHERE v.Kurzbezeichnung=b.Verlag
ORDER BY 1 ASC

Um die Schwierigkeit weiter zu erhöhen, werden jetzt Funktionen eingefügt.

3. Wieviele Bücher führen die jeweiligen Verlage im Buchladen und was ist der jeweilige Durchschnittspreis pro Verlag?

SELECT v.Name, COUNT(b.Nr) AS Menge, AVG(b.Preis) AS Durchschnittspreis
FROM Verlag v, Buch b
WHERE v.Kurzbezeichnung=b.Verlag
GROUP BY 1
ORDER BY 1 ASC

Ein zweites Beispiel zu den möglichen Funktionen soll durch weitere Bedingungen und Ausweitung der benötigten Daten an Komplexität gewinnen.

4. Rechne die durchschnittliche Auftragssumme der oberösterreichischen Auftragsgeber aus, welche sich nach den ersten 3000 Aufträgen ergab!

SELECT p.Region, AVG(b.Preis*a.Menge)
FROM Auftragspos a, PLZ p, Kunde k, Auftrag aa, Buch b
WHERE p.Plz=k.Plz AND k.Nr=aa.Kundennummer AND aa.Nr=a.Auftragsnummer AND a.Buchnummer=b.Nr AND a.Auftragsnummer<=3000 AND p.Region="O"
GROUP BY 1

Jetzt soll noch eine Havingfunktion eingebaut werden.

5. Gib alle Bundesländer an, die über eine durchschnittliche Auftragssumme von über 1000 Euro haben bei allen Aufträgen mit einer Auftragsnummer von 100 bis 1000! Führe auch die jeweiligen Durchschnittsbeträge an und wieviele Bücher die Aufträge durchschnittlich und insgesamt enthielten. Gliedere Sie außerdem absteigend mit ihren durchschnittlichen Auftragssummen.

Select p.Region, AVG(b.Preis*a.Menge) AS Durchschnittsauftragssumme, AVG(a.Menge) AS Durchschnittsmenge, SUM(a.Menge) AS Gesamtmenge
FROM PLZ p, Kunde k, Auftrag aa, Auftragspos a, Buch b
WHERE p.Plz=k.Plz AND k.Nr=aa.Kundennummer AND aa.Nr=a.Auftragsnummer AND a.Buchnummer=b.Nr AND a.Auftragsnummer BETWEEN 100 AND 1000
GROUP BY 1
HAVING Durchschnittsauftragssumme>1000
ORDER BY 2 DESC

Ich hab beim Tutorium diese Woche einiges gelernt und hoffe, dass ich bei den Abfragen keine Fehler gemacht habe. Hat wirklich Spaß gemacht, die Lösungen zu meinen eigenen Aufgaben zu finden.

Bis zum nächsten Eintrag,

mfg
Roland