Quick Ref.
Functions
date
number
string
Data Types
date
number
string

ACME Buchhaltung

badguy (id, name, address)
product (id, description, price)
receipt (badguy, rdate, notes, amount) 
shipped (badguy, sdate, product, quantity)

Einige Details zur Datenbank.

Einfache Anfragen an die Datenbank

1a. Ermittle die Kunden 'C001', 'C005'.

Results
1b. Ermittle die Produkte 'P001', 'P007'.

Results

Rechnungen und Quittungen

2a. Welche Zahlungen hat der Kunde 'C001' geleistet?

Results
2b. Zeige alle Lieferungen an den Kunden 'C001', nach dem Datum sortiert.

Results

Aufbereiten der Kontendaten

3a. Erstelle eine Liste aller Produkte, die der Kunde 'C001' gekauft hat. Die Liste soll enthalten: Datum, Produktbeschreibung, Stückpreis, die versendete Menge und den Gesamtwert (quantity * price).

Results
3b. Berechne den Gesamtwert aller Produkte, die an den Kunden 'C001' am 23. Juli 1998 versandt wurden.
Hinweis: Lt. SQL-Standard wird das Datum so geschrieben: DATE '1998-07-23'

Results
3c. Bereite eine Versandbestätigung für den Kunden 'C001' vor. Sie soll das Datum, den Hinweis 'Delivery' (Lieferung) und den Gesamtwert der versandten Produkte pro Tag enthalten.

Results
3d. Erzeuge eine Empfangsbestätigung für den Kunden 'C001' vor. Es sollen das Datum, Anmerkungen (notes) und die empfangene Menge angezeigt werden.

Results
3e. Um eine komplette Versand- und Empfangsbestätigung für den Kunden 'C001' aufzubereiten, ist der UNION-Operator zu verwenden. Die Bestätigung soll wie unten dargestellt aussehen.
Anmerkung: Die unten gezeigten Werte sind nicht korrekt. Sie dienen nur als Beispiel. Für leere Werte kann '' oder NULL verwendet werden, wenn nötig auch 0.

Results 
  10-JUL-1998 Delivery       100.00
  15-JUL-1998 Cheque                       100.00
  22-JUL-1998 Delivery       210.00
  23-JUL-1998 Delivery        45.00
  23-JUL-1998 Cash                         255.00

Für die folgenen Aufgaben wird die VIEW Kontopositionen (accountline) benötigt. (Falls nicht verfügbar, bitte selbst erstellen.) Sie zeigt eine Zeile für jede Aus- oder Einzahlung. Geldausgänge (Schulden) sind negativ, Geldeingänge (Gutschriften) sind positiv dargestellt.

CREATE VIEW accountline AS
  SELECT shipped.badguy AS badguy, shipped.sdate AS LineDate,
        'Delivery' AS Legend, -[price]*[quantity] AS amount
    FROM product, shipped 
    WHERE product.id = shipped.product
UNION
  SELECT receipt.badguy, receipt.rdate,notes, amount
    FROM receipt
    ORDER BY linedate;
4a. Erstelle eine Anweisung, die alle Kontopositionen zeigt.

Results
4b. Erzeuge eine Liste der unbeglichenen Beträge für jeden Kunden.

Results

Was stimmt nicht mit dieser Datenbank?

  1. Es gibt keinen Mechanismus, um Rücksendungen aufzuzeichnen. Welche Maßnahmen sollten durchgeführt werden, wenn fehlerhafte Waren zurückgesandt und Geld zurückerstattet werden soll?
  2. Der Preis des Produktes P001 soll erhöht werden. Welche Auswirkungen hat dies auf den berechneten Kontostand für Kunden, die das Produkt kurz zuvor gekauft haben? Wie sollte die Struktur der Datenbank geändert werden, um dieses Problem zu verhindern?
  3. Alle Waren müssen zu einem Listenpreis eingekauft werden. In der Praxis können fallweise Preisnachlässe gegeben werden. Wie könnten solche Preisnachlässe in der Datenbank aufgezeichnet werden?
  4. Mit zunehmendem Datenbestand wäre eine Routine zur Konsolidierung wünschenswert, die alte Einträge entfernt und dafür nur den unbeglichenen Betrag je Kunde hinterläßt (Saldovortrag). Andererseits müßten für eine kleine Firma wohl Jahre/Jahrzehnte an Daten gespeichert werden, bevor es sich negativ in der Bearbeitungszeit oder der Speicherkapazität bemerkbar macht.

Im nächsten Tutorium werden am Beispiel der Buslinien in Edinburgh der Self Join und komplexe SQL-Abfragen geübt.