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:

1. Geben Sie aus wieviele Flaschen jeder Winzer von seiner Ernte 2002 pro Sorte höchstens verkaufen kann.
2. 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.
   
3. Geben Sie eine Liste aus, in der pro Bestellung jedes Kunden der Verkaufspreis der Bestellung vermerkt ist.
4. Geben Sie ID, Name und Adresse jener Kunden aus, die noch nie "Grünen Veltliner" bestellt haben.
5. Geben Sie ID und Name jener Kunden aus, die bereits "Blauen Portogieser", aber noch nie "Uhudler" bestellt haben.
6. Geben Sie Name und Adresse jener Winzer aus, die bereits alle im Programm des Händlers befindlichen Sorten geerntet haben.
   
7. 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).
8. Geben Sie Paare von Winzern aus, welche im Jahr 2002 genau dieselben Sorten geerntet haben. Dabei soll jede Paarung nur einmal ausgegeben werden.
9. Gesucht sind Sorten und die Namen jener Kunden, welche von dieser Sorte schon mehr als 100 Flaschen bestellt haben.
10. 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.
11. 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.
12. Geben Sie die Kunden aus, die bisher die zweitmeisten Flaschen von einem Winzer namens "Alois Reblaus" bestellt haben.