Data Engineering für Wirtschaftsinformatiker
181.078 UE 2.0
Sommersemester 2003
Beispiel 4
alle Gruppen
SQL
Das folgende Beispiel wurde am 11. Juni 2003 ausgegeben.
Die Lösungen sind persönlich bei Thomas Havelka zu präsentieren
(Termin auf Anfrage).
Weinhandel
Für einen Weinhändler soll eine Datenbank implementiert und
einige Abfragen in SQL geschrieben werden.
Die Datenbank finden Sie auf minteka.dbai.tuwien.ac.at.
Der Rechner ist mittels ssh erreichbar.
Login und Passwort sind jeweils 'g' gefolgt von Ihrer zweistelligen Gruppennummer
(also z.B. 'g99', Gruppen 1-9 bitte eine '0' vor die Gruppennummer stellen,
für die Gruppe 5 also 'g05').
Die Datenbank wird mittels 'sqlplus /' gestartet.
Die Datenbank soll aus folgenden Relationen bestehen:
Weinsorte (Name, Anmerkung)
Winzer (Code, Name, Adresse)
Ernte (Weinsorte.Name, Winzer.Code, Jahr, Menge, Preis)
Kunde (ID, Name, Adresse, Kontostand)
bevorzugt (Kunden.Id, Weinsorte.Name)
Bestellung (Kunden.Id, Datum, Nummer, Weinsorte.Name, Winzer.Code, Jahr, Anzahl) -- Änderung 12. 6. 2003
Anmerkungen:
- Winzer haben einen eindeutigen, von der Winzervereinigung vergebenen
Code.
- Winzer "ernten" jedes Jahr von verschiedenen Sorten eine bestimmte
Menge Wein (in Litern), den Sie dann zu einem Flaschenpreis (0.75 Liter)
verkaufen.
- Kunden des Weinhändlers sind jene Leute, die dem Händler
Wein abkaufen. Da diese den Wein nicht immer sofort bezahlen, wird ein
Kontostand vermerkt, der angibt, ob der Kunde Schulden beim Händler
hat. Natürlich kann auch im voraus bezahlt werden, dann hat der Kunde
einen positiven Kontostand.
- Für die Kunden werden die Sorten vermerkt, die er als seine
bevorzugten angibt.
- Bei Bestellungen wird die Anzahl der bestellten Flaschen angegeben.
Jeder Kunde kann pro Tag eine Bestellung aufgeben,
bei der die Posten durchnummeriert sind. (Ergänzung 12. 6. 2003)
- Der Verkaufspreis der Weine beträgt 150% vom Einkaufspreis.
Implementieren Sie diese Relationen als SQL Tables und füllen Sie
diese mit Testdaten, anhand derer Sie die folgenden Abfragen überprüfen
können.
Schreiben Sie folgende Abfragen in SQL(Sortieren Sie die Ergebnisse jeweils
nach sinnvollen Kriterien). Dabei dürfen Views verwendet werden:
- Geben Sie aus wieviele Flaschen jeder Winzer von seiner Ernte 2002
pro Sorte höchstens verkaufen kann.
- Geben Sie an, wieviele Liter von den verschiedenen Sorten im Durchschnitt
pro Jahr geerntet werden, dabei sollen auch Sorten gelistet werden, welche
zwar in der Datenbank aufscheinen, die jedoch nicht angebaut/geerntet werden.
- Geben Sie eine Liste aus, in der pro Bestellung jedes Kunden der
Verkaufspreis der Bestellung vermerkt ist.
- Geben Sie ID, Name und Adresse jener Kunden aus, die noch nie "Grünen
Veltliner" bestellt haben.
- Geben Sie ID und Name jener Kunden aus, die bereits "Blauen Portogieser",
aber noch nie "Uhudler" bestellt haben.
- Geben Sie Name und Adresse jener Winzer aus, die bereits alle im
Programm des Händlers befindlichen Sorten geerntet haben.
- Geben Sie aus, wie man die Relation bevorzugt ändern
sollte: Gibt es Sorten, die ein Kunde in grossen Mengen bestellt, die für
ihn aber nicht in der Relation bevorzugt aufscheinen (Als Schranke
für "grosse Mengen" könnten Sie z.B. die durchschnittlich bestellte
Menge seiner bevorzugten Sorten nehmen).
- Geben Sie Paare von Winzern aus, welche im Jahr 2002 genau dieselben
Sorten geerntet haben. Dabei soll jede Paarung nur einmal ausgegeben werden.
- Gesucht sind Sorten und die Namen jener Kunden, welche von dieser
Sorte schon mehr als 100 Flaschen bestellt haben.
- Geben Sie eine Liste aus, in der steht, wieviele Liter der Sorten
"Grüner Veltliner", "Zweigelt" und "Welschriesling" von den verschiedenen
Winzern in den Jahren 1995 bis 1999 geerntet wurden. In der Liste sollen
in einer Zeile der Name des Winzers sowie die Mengenangaben für
alle 3 Sorten stehen. Beachten Sie, dass nicht jeder Winzer alle 3 Sorten
ernten muss.
- Geben Sie für jede Sorte Wein den Winzer aus, der von dieser
Sorte im Jahr 2001 die grösste Menge Wein produziert hat, sowie die
von ihm produzierte Menge.
- Geben Sie die Kunden aus, die bisher die zweitmeisten Flaschen
von einem Winzer namens "Alois Reblaus" bestellt haben.