Als Grundlage für diese Aufgabe dient die 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

1. Dieser Befehl sollte nun um den Umsatz pro Kunde erweitert werden ohne dass sich die Anzahl der Aufträge verändert.

Hierzu reicht es nicht aus den Befehl "SUM(Auftragspos.Menge*Buch.Preis)" einzufügen, sondern auch der Befehl "COUNT(Auftrag.Nr)" gehört zu "COUNT(DISTINCT Auftrag.Nr)" erweitert, damit jede Auftragsnummer nur einmal gezählt wird.

Die Lösung lautet also:

SELECT Kunde.Nr, Kunde.Nachname, COUNT(DISTINCT Auftrag.Nr) as "Anzahl Aufträge", SUM(Auftragspos.Menge*Buch.Preis) as "Umsatz/Kunde"
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

2. Als nächstes sollte die Verteilung der Aufträge über die Zeit (Jahre) ermittelt werden, ohne dass Berechnungen durchgeführt werden.

Gründsätzlich reicht dazu aus den Befehl:

SELECT Nr, Datum
FROM Auftrag
ORDER by Datum

einzugeben, jedoch werden hier nur die ersten 500 Aufträge angezeigt. Also fügen wir zu der Abfrage den Befehl "WHERE YEAR(Datum) = "Jahr"" hinzu, um die Aufträge eines bestimmten Jahres zu ermitteln.

Die Lösung lautet (z.B:für 2001):

SELECT Nr, Datum
FROM Auftrag
WHERE YEAR(Datum) = "2001"
ORDER by Datum

3. Jetzt sollte die Lösung der 1.Aufgabenstellung so umgeformt werden dass wir nur die Daten für einen sinnvollen Zeitraum erhalten.

Hierzu habe ich mich für das erste Quartal im Jahr 2000 entschieden und habe die Abfrage wie folgt durchgeführt:

SELECT Kunde.Nr, Kunde.Nachname, COUNT(DISTINCT Auftrag.Nr) as "Anzahl Aufträge", SUM(Auftragspos.Menge*Buch.Preis) as "Umsatz/Kunde"
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-03-31"
GROUP BY Kunde.Nr
ORDER BY Kunde.Nr

Als Ergebnis bekomme ich somit die Anzahl der Aufträge und den Umsatz pro Kunden im ersten Quartal.

4. Zuletzt sollte noch ein weiterer Filter in die Abfrage eingefügt werden der sich an der durchschnittlichen Anzahl der Aufträge orientiert.

Hierzu habe ich die Berechnung "COUNT(DISTINCT Auftrag.Nr)/3" hinzugefügt um die durchschnittliche Anzahl an Aufträgen pro Monat im ersten Quartal 2000 zu ermitteln.

Die Lösung lautet somit:

SELECT Kunde.Nr, Kunde.Nachname, COUNT(DISTINCT Auftrag.Nr) as "Anzahl Aufträge", SUM(Auftragspos.Menge*Buch.Preis) as "Umsatz/Kunde", COUNT(DISTINCT Auftrag.Nr)/3 as "durchschnittliche monatliche 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 BETWEEN "2000-01-01" AND "2000-03-31"
GROUP BY Kunde.Nr
ORDER BY Kunde.Nr

Die Aufgabe vom 12.03.2008 war es 5 Abfragen mit steigendem Schwierigkeitsgrad zu erstellen, was ich hiermit getan habe.

1.Aufgabe:
Erstelle eine Liste aller Kunden mit der Postleitzahl 4030, sortiert nach dem Nachnamen!

Lösung:
Select *
from Kunde
where Plz=4030
order by Nachname

2.Aufgabe:
Erstelle eine Liste aller Kunden die im Jahr 2003 bestellt haben!

Lösung:
Select Kunde.Nachname, Kunde.Vorname
from Kunde, Auftrag
where Auftrag.Kundennummer=Kunde.Nr
and Auftrag.Datum like "%2003%"
group by Kunde.Nachname, Kunde.Vorname
order by Kunde.Nachname

3.Aufgabe:
Zähle die Kunden mit der Postleitzahl 4020!

Lösung:
Select Count(Kunde.Nr) as "Anzahl Kunden 4020"
from Kunde, PLZ
where PLZ.Plz=Kunde.Plz
and PLZ.Plz=4020

4.Aufgabe:
Berechne den Gesamtbestellwert von Auftrag 4!

Lösung:
Select Sum(Auftragspos.Menge*Buch.Preis) as "Gesamtbestellwert"
from Auftragspos, Buch
where Auftragspos.Buchnummer=Buch.Nr
and Auftragspos.Auftragsnummer=4

5.Aufgabe:
Berechne den Durchschnittlichen Bestellwert der Kundin Viktoria Kalker!

Lösung:
Select AVG(Auftragspos.Menge*Buch.Preis)as "Durchschnittlicher Bestellwert"
from Auftrag, Auftragspos, Buch, Kunde
where Auftrag.Nr=Auftragspos.Auftragsnummer
and Auftragspos.Buchnummer=Buch.Nr
and Kunde.Nr=Auftrag.Kundennummer
and Kunde.Nachname="Kalker"
and Kunde.Vorname="Viktoria"

Ich habe zwar sämtliche Abfragen auf SQL.IDV.EDU überprüft, aber dennoch ist es möglich dass sich in meine Abfragen Fehler eingeschlichen haben. Ich bitte euch, falls ihr Fehler entdeckt, mir kurz Bescheid zu geben damit ich die Abfragen korrigieren kann.

Lg
Thomas

Ich habe soeben ein echt nützliches Tool entdeckt, mit dem man immer die neuesten Weblog-Einträge aus IV II abfragen kann.

Hier der Link zum Weblog von Franz Heinzl

Ich wünsche noch schöne Ferien.
Lg
Thomas