SQL-Problem vom 02. April 2008
christian.weinzinger.Uni-Linz, Mittwoch, 2. April 2008, 12:02
Betrifft Punkt 1 der Aufgabenstellung:
Meiner Ansicht nach ist die folgende SQL-Anweisung, diejenige welche den gefragten Sachverhalt beschreibt.
Sie liefert als Ergebnis 24 Tupel.
Sie liefert nämlich aus das gleiche Ergebnis wie folgende Abfrage. Es wurden aber die Summe bzw. die beiden Tabellen Buch und Auftragspos entfernt. Würde die vorige Abfrage falsch sein, dann würden durch die 2 weggefallenen Joins andere Ergebnistupel geliefert werden --> man erwartet aber, dass das gleiche Ergebnis zurückkommt (wenn man annimmt, dass die erste Abfrage, dass gleiche Ergebnis geliefert hat)
Das Ergebnis sind 24 Tupel mit den gleichen Werten und Namen
Geht man nun auf die höchste Ebene der Auswertungsmöglichkeit - nämlich der Abfrage wie viele Aufträge nach dem 01. Jänner 2001 eingegangen sind - dann erhält man als Ergebnis 24!!
Somit ist bewiesen, dass die Abfragen richtig sind.
edit - korrigierte Aufgabenstellung -----------------
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
sollte eine Weiterentwicklung der Abfragen stattfinden.
1)
Es soll der Umsatz noch zusätzlich zur Anzahl der Aufträge in die Abfrage eingebaut werden.
Ich habe das mit dieser Abfrage bewerkstelligt. Das Ergebnis (=Anzahl Tupel) ist das gleich wie vorhing.
2) und 3)
Als nächstes sollte eine Verteilung über die Zeit stattfinden (ohne Anzahl und Umsatz der Aufträge).
Ich habe eine Verteilung der Aufträge in Jahr vorgenommen. Eine Verteilung über Einschränkungen in der Where-Klausel sind weiter oben zu sehen. Diese wollte ich nun komprimieren und erweitern.
Da die Lehrdatenbank leider keine gängige SQL-Syntax wie zB in Oracle SQL oder MySQL (nämlich die Durchführung von SubQueries) zulässt, musste ich diese Abfrage etwas komplizierter gestalten.
Da ich bei SubQueries immer eine Fehlermeldung 'use joins to connect all tables' erhalten habe und ich nicht weiß wie man mittels joins in einer Abfrage 2 mal die gleiche Tabelle mit unterschiedlichen Spalten referenzieren soll, half ich mir mit Funktionen.
Ich wollte nämlich in 2 getrennten Spalten die Anzahl der Aufträge je Jahr angezeigt haben.
Also nam ich die Funktion Year(<Date>), die mir das jeweilige Jahr des Datums liefert und zählte für die Spalte 2000 das Vorkommen des Jahres 2000 bzw. für 2001 das Vorkommen des Jahres 2001.
Die folgende Anweisung erzeugt genau das gewollte Ergebnis:
Wiederum ändert sich die Anzahl der Tupel nicht. Als nächstes habe ich noch die Anzahl aller Aufträge und den Gesamtumsatz eingebunden.
4)
Als nächstes sollten noch die durchschnittliche Anzahl von Aufträge ausgewertet werden. Ich muss gestehen, dass die Aufgabenstellung für mich sehr unverständlich geschrieben ist, da weder ein Zeitraum angegeben ist noch angegeben ist welcher Durchschnitt berechnet werden soll.
Der Durchschnitt aller Aufträge, die je gegeben wurden macht eher wenig Sinn. Durchschnittlicher Umsatz je Auftrag macht schon Sinn, aber nicht im Zusammenhang mit oben angeführter Auswertung. Es bringt nämlich nicht, wenn in jeder Zeile zusätzlich noch der durchschnittliche Umsatz je Auftrag angeführt wird. Der ist nämlich logischerweise in jeder Zeile gleich.
Ich habe mich also dazu entschlossen den durchschnittlichen Umsatz je Kunde zu errechnen. Ich hab diese Abfrage nicht mit der AVG() Funktion durchgeführt, da sonst ein falsches Ergebnis geliefert wird.
Zuletzt zeige ich noch, dass die Abfragen auch stimmen. Ich filtere jetzt nur jene Kunden, die in beiden Jahren Aufträge an uns gegeben haben. Dazu ändere ich in der letzte Zeile (=HAVING-Klausel) das OR zu einem AND.
Schränkt man die HAVING-Klausel auf nur Anzahl2001>0 ein, dann erhält man wieder 24 Tupel. Genau wie in allen Abfragen davor, die 2001er Aufträge abgefragt haben.
Meiner Ansicht nach ist die folgende SQL-Anweisung, diejenige welche den gefragten Sachverhalt beschreibt.
| SELECT Kunde.Nr, Kunde.Nachname, Count(Auftrag.Nr) As Anzahl, Sum(Auftragspos.Menge*Buch.Preis) as Auftragssumme 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' GROUP BY Kunde.Nr |
Sie liefert als Ergebnis 24 Tupel.
Sie liefert nämlich aus das gleiche Ergebnis wie folgende Abfrage. Es wurden aber die Summe bzw. die beiden Tabellen Buch und Auftragspos entfernt. Würde die vorige Abfrage falsch sein, dann würden durch die 2 weggefallenen Joins andere Ergebnistupel geliefert werden --> man erwartet aber, dass das gleiche Ergebnis zurückkommt (wenn man annimmt, dass die erste Abfrage, dass gleiche Ergebnis geliefert hat)
Das Ergebnis sind 24 Tupel mit den gleichen Werten und Namen
| SELECT Kunde.Nr, Kunde.Nachname, Count(Auftrag.Nr) As Anzahl FROM Kunde, Auftrag WHERE Kunde.Nr = Auftrag.Kundennummer AND Auftrag.Datum > '2001-01-01' GROUP BY Kunde.Nr |
Geht man nun auf die höchste Ebene der Auswertungsmöglichkeit - nämlich der Abfrage wie viele Aufträge nach dem 01. Jänner 2001 eingegangen sind - dann erhält man als Ergebnis 24!!
| select count(Auftrag.Nr) from Auftrag where Auftrag.Datum > '2001-01-01' |
Somit ist bewiesen, dass die Abfragen richtig sind.
edit - korrigierte Aufgabenstellung -----------------
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
sollte eine Weiterentwicklung der Abfragen stattfinden.
1)
Es soll der Umsatz noch zusätzlich zur Anzahl der Aufträge in die Abfrage eingebaut werden.
Ich habe das mit dieser Abfrage bewerkstelligt. Das Ergebnis (=Anzahl Tupel) ist das gleich wie vorhing.
| SELECT Kunde.Nr, Kunde.Nachname, COUNT(Auftrag.Nr) as Anzahl, sum(Auftragspos.Menge*Buch.Preis) as Auftragssumme 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) und 3)
Als nächstes sollte eine Verteilung über die Zeit stattfinden (ohne Anzahl und Umsatz der Aufträge).
Ich habe eine Verteilung der Aufträge in Jahr vorgenommen. Eine Verteilung über Einschränkungen in der Where-Klausel sind weiter oben zu sehen. Diese wollte ich nun komprimieren und erweitern.
Da die Lehrdatenbank leider keine gängige SQL-Syntax wie zB in Oracle SQL oder MySQL (nämlich die Durchführung von SubQueries) zulässt, musste ich diese Abfrage etwas komplizierter gestalten.
Da ich bei SubQueries immer eine Fehlermeldung 'use joins to connect all tables' erhalten habe und ich nicht weiß wie man mittels joins in einer Abfrage 2 mal die gleiche Tabelle mit unterschiedlichen Spalten referenzieren soll, half ich mir mit Funktionen.
Ich wollte nämlich in 2 getrennten Spalten die Anzahl der Aufträge je Jahr angezeigt haben.
Also nam ich die Funktion Year(<Date>), die mir das jeweilige Jahr des Datums liefert und zählte für die Spalte 2000 das Vorkommen des Jahres 2000 bzw. für 2001 das Vorkommen des Jahres 2001.
Die folgende Anweisung erzeugt genau das gewollte Ergebnis:
| SELECT Kunde.Nr, Kunde.Nachname, sum(year(Auftrag.Datum)=2000) as Aufträge2000, sum(year(Auftrag.Datum)=2001) as Aufträge2001 FROM Kunde, Auftrag, Auftragspos, Buch WHERE Kunde.Nr = Auftrag.Kundennummer AND Auftrag.Nr = Auftragspos.Auftragsnummer AND Auftragspos.Buchnummer = Buch.Nr GROUP BY Kunde.Nr having Aufträge2001>0 or Aufträge2000>0 |
Wiederum ändert sich die Anzahl der Tupel nicht. Als nächstes habe ich noch die Anzahl aller Aufträge und den Gesamtumsatz eingebunden.
| SELECT Kunde.Nr, Kunde.Nachname, sum(year(Auftrag.Datum)=2000) as Anzahl2000, sum(year(Auftrag.Datum)=2001) as Anzahl2001, count(Auftrag.Datum) as Gesamtanzahl, sum(Auftragspos.Menge*Buch.Preis) as Gesamtumsatz FROM Kunde, Auftrag, Auftragspos, Buch WHERE Kunde.Nr = Auftrag.Kundennummer AND Auftrag.Nr = Auftragspos.Auftragsnummer AND Auftragspos.Buchnummer = Buch.Nr GROUP BY Kunde.Nr having Anzahl2001>0 or anzahl2000>0 |
4)
Als nächstes sollten noch die durchschnittliche Anzahl von Aufträge ausgewertet werden. Ich muss gestehen, dass die Aufgabenstellung für mich sehr unverständlich geschrieben ist, da weder ein Zeitraum angegeben ist noch angegeben ist welcher Durchschnitt berechnet werden soll.
Der Durchschnitt aller Aufträge, die je gegeben wurden macht eher wenig Sinn. Durchschnittlicher Umsatz je Auftrag macht schon Sinn, aber nicht im Zusammenhang mit oben angeführter Auswertung. Es bringt nämlich nicht, wenn in jeder Zeile zusätzlich noch der durchschnittliche Umsatz je Auftrag angeführt wird. Der ist nämlich logischerweise in jeder Zeile gleich.
Ich habe mich also dazu entschlossen den durchschnittlichen Umsatz je Kunde zu errechnen. Ich hab diese Abfrage nicht mit der AVG() Funktion durchgeführt, da sonst ein falsches Ergebnis geliefert wird.
| SELECT Kunde.Nr, Kunde.Nachname, sum(year(Auftrag.Datum)=2000) as Anzahl2000, sum(year(Auftrag.Datum)=2001) as Anzahl2001, count(Auftrag.Datum) as Gesamtanzahl, sum(Auftragspos.Menge*Buch.Preis) as Gesamtumsatz, (sum(Auftragspos.Menge*Buch.Preis) / count(Auftrag.Datum)) as Durchschnittsumsatz FROM Kunde, Auftrag, Auftragspos, Buch WHERE Kunde.Nr = Auftrag.Kundennummer AND Auftrag.Nr = Auftragspos.Auftragsnummer AND Auftragspos.Buchnummer = Buch.Nr GROUP BY Kunde.Nr having Anzahl2001>0 or anzahl2000>0 |
Zuletzt zeige ich noch, dass die Abfragen auch stimmen. Ich filtere jetzt nur jene Kunden, die in beiden Jahren Aufträge an uns gegeben haben. Dazu ändere ich in der letzte Zeile (=HAVING-Klausel) das OR zu einem AND.
| SELECT Kunde.Nr, Kunde.Nachname, sum(year(Auftrag.Datum)=2000) as Anzahl2000, sum(year(Auftrag.Datum)=2001) as Anzahl2001, count(Auftrag.Datum) as Gesamtanzahl, sum(Auftragspos.Menge*Buch.Preis) as Gesamtumsatz, (sum(Auftragspos.Menge*Buch.Preis) / count(Auftrag.Datum)) as Durchschnittsumsatz FROM Kunde, Auftrag, Auftragspos, Buch WHERE Kunde.Nr = Auftrag.Kundennummer AND Auftrag.Nr = Auftragspos.Auftragsnummer AND Auftragspos.Buchnummer = Buch.Nr GROUP BY Kunde.Nr having Anzahl2001>0 and anzahl2000>0 |
Schränkt man die HAVING-Klausel auf nur Anzahl2001>0 ein, dann erhält man wieder 24 Tupel. Genau wie in allen Abfragen davor, die 2001er Aufträge abgefragt haben.
