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:

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.