Die 1. Hausübung vom Tutorium am 1. April enthält folgende Aufgabe für eine Exceltabelle:
+ Textfeld
+ Diagramm
+ Zellenumrandungen
+ Grafik

Diese Excel-Datei enthält meine Lösung.

Als Ausgangspunkt unserer Aufgabe hatten wir diese Angabe:

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

Schritt 1
Ergänzen sie obenstehende Abfrage um den Umsatz (Preis * Menge) je Kunde. Achten Sie darauf, dass die Anzahl der getätigten Aufträge "COUNT(Auftrag.Nr)" korrekt ist, bzw. im Vergleich zur Ausgangssituation unverändert.

SELECT Kunde.Nr, Kunde.Nachname, COUNT(Auftrag.Nr), SUM(Buch.Preis*Auftragspos.Menge)
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

Mir ist nach dieser Abfrage aufgefallen, dass die Anzahl der Aufträge ansteigt. Nach langem überlegen und ausprobieren, bin ich glaub ich auf die Lösung gekommen, die wie folgt lautet:

SELECT Kunde.Nr, Kunde.Nachname, COUNT(DISTINCT Auftrag.Nr), 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

Ich habe bei dem Befehl Count noch ein Distinct eingefügt, weil sonst die Anzahl der Aufträge ansteigt.

Schritt 2
Ermitteln Sie in einem Weiteren Schritt die Verteilung der Aufträge über die Zeit (Jahre), OHNE dass dabei Auswertungen (Anzahl der Aufträge bzw. Auftragssummen) ermittelt werden.

SELECT Kunde.Nr, Kunde.Nachname, Auftrag.Datum
FROM Kunde, Auftrag
WHERE Kunde.Nr = Auftrag.Kundennummer
GROUP BY Kunde.Nr
ORDER BY Kunde.Nr

Schritt 3
Grenzen Sie die Abfrage 1 in sinnvolle Zeitabschnitte ein.

SELECT Kunde.Nr, Kunde.Nachname, COUNT(DISTINCT Auftrag.Nr), 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
AND Auftrag.Datum >= '2000-01-01'
AND Auftrag.Datum <= '2000-01-31'
GROUP BY Kunde.Nr
ORDER BY Kunde.Nr

Ich habe den Zeitraum auf den Monat Jänner 2000 eingeschränkt.

Schritt 4
Ergänzen Sie die zeitlich abgegrenzten Auswertungen um einen weiteren Filter, der sich an der durchschnittlichen Anzahl der Aufträge orientiert.

SELECT Kunde.Nr, Kunde.Nachname, COUNT(DISTINCT Auftrag.Nr), SUM(Buch.Preis*Auftragspos.Menge) AS Umsatz, AVG(Auftragspos.Menge)
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-01-31'
GROUP BY Kunde.Nr
ORDER BY Kunde.Nr

Bei der Berechnung der durchschnittlichen Anzahl der Aufträge bin ich mir nicht sicher.

Bei der zweiten Aufgabe sollen wir unsere SQL Kenntnisse vorweisen. Dafür sollen wir mindestens 5 Abfragen mittels SQL durchführen. Der Schwierigkeitsgrad soll dabei gesteigert werden.

Beispiel 1 - Liste aller verfügbaren Bücher unter Angabe des Preises, des Verlages und des Bestandes.

SELECT Buch.Titel, Verlag.Name, Buch.Preis, Buch.Bestand
FROM Verlag, Buch
WHERE Verlag.Kurzbezeichnung = Buch.Verlag

Beispiel 2 –Liste alle Bücher mit Autor und Titel eines Autors auf dessen Namen mit einem „A“ beginnt auf. Autor aufsteigend sortiert

SELECT Autor, Titel
FROM Buch
WHERE Autor LIKE "A%"
ORDER BY Autor ASC

Beispiel 3 – Liste alle Kunden, die in Niederösterreich, in Oberösterreich oder im Salzburg wohnen auf. Sortiert nach der Region

SELECT Kunde.Nachname, Kunde.Vorname, PLZ.Region
FROM Kunde, PLZ
WHERE (Kunde.Plz=PLZ.Plz)
AND (PLZ.Region='N' OR PLZ.Region='O' OR PLZ.Region='Sa')
ORDER BY PLZ.Region

Beispiel 4 – Liste alle Kunden die aus Graz kommen und um welchen Wert sie Bücher bestellt haben auf. Wert absteigend sortiert.

SELECT Kunde.Nachname, Kunde.Vorname, PLZ.Ort, SUM(Artikel.Menge * Buch.Preis)
FROM Kunde, Auftrag, PLZ, Artikel, Buch
WHERE Kunde.Nr = Auftrag.Kundennummer AND
Auftrag.Nr = Artikel.Auftragsnummer AND
Buch.Nr=Artikel.Buchnummer AND
Kunde.Plz = PLZ.Plz
AND PLZ.Ort LIKE 'Graz%'
GROUP BY Kunde.Nr
ORDER BY SUM(Artikel.Menge * Buch.Preis) DESC

Beispiel 5 - Liste alle Kunden mit Kundennummer und Umsatz > 90.000 auf. Groupiert nach Kundennummer und Umsatz absteigend sortiert

SELECT Kunde.Nr, Nachname, SUM(Menge*Preis) 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
HAVING Umsatz > 90000
ORDER BY Umsatz DESC