2. ¨Ubung - Datenbankprogrammierung

Professur für Datenbanken
Fakultät Informatik
Institut für Systemarchitektur
2. Übung - Datenbankprogrammierung
”
(Noch mehr) SQL“
Ziel der Übung. Vertiefung der Kenntnisse der SQL-Anfragesprache.
Aufgaben
Für diese für diese Übung wird die TPC-H-Datenbank verwendet, falls nicht anders angegeben.
1. Aufgabe: Modularisierung von Anfragen
1. Ermitteln Sie alle afrikanischen Zulieferer (S NAME)! Verwenden Sie eine unkorrelierte
Unterabfrage und vermeiden Sie eine Verbundoperation in der äußeren Abfrage! [21]
2. Ermitteln Sie die Namen der Kunden (C NAME), deren Kontostand (C ACCTBAL) höher
ist als der Kontostand aller anderen Kunden im selben Marktsegment (C MKTSEGMENT)!
Verwenden Sie eine korrelierte Unterabfrage! [3]
3. Ermitteln Sie die Bestellnummer (O ORDERKEY), das Bestelldatum (O ORDERDATE) und
den Zustand der Bestellung (O ORDERSTATUS) aller Bestellungen von französichen Kunden! Verwenden Sie dazu eine Nested Table Expression, welche die Kundennummern
aller französischen Kunden beinhaltet! [47]
4. Ermitteln Sie alle Zulieferer (SUPPLIER.S NAME) und die Anzahl der Stahlteile (P TYPE),
die der jeweilige Zulieferer momentan im Lager hält (PS AVAILQTY)! Verwenden Sie
eine Common Table Expression, welche für jeden Zuliefer (PS SUPPKEY) die Anzahl der
verfügbaren Stahlteile beinhaltet! Sortieren Sie das Ergebnis aufsteigend nach dieser
Anzahl! [100]
5. Ermitteln Sie für jedes Teil (P NAME), wie oft es in den Lagern (PS AVAILQTY) aller
Zulieferer zusammen verfügbar ist! Verwenden Sie dazu ein Scalar Full Select! Sortieren
Sie das Ergebnis absteigend nach der Verfügbarkeit! [2000]
6. Bonusaufgabe: Verbinden Sie sich mit der SAMPLE-Datenbank! Ermitteln Sie rekursiv
für jedes Projekt (PROJNO) alle übergeordneten Projekte (MAJPROJ)! [29]
Die folgende Tabelle zeigt einen Ausschnitt der Ergebnismenge:
PROJNO
--------AD3100
AD3110
AD3111
AD3111
...
MAJPROJ
---------AD3100
AD3100
AD3110
1
Professur für Datenbanken
Fakultät Informatik
Institut für Systemarchitektur
2. Aufgabe: Umsatz nach Regionen
Erstellen Sie eine Sicht REGION TURNOVER, in welcher der Umsatz pro Region (R NAME) des
Zulieferers und Region des Kunden berechnet wird! Der Umsatz berechnet sich wie folgt:
SUM(LINEITEM.L EXTENDEDPRICE*(1-LINEITEM.L DISCOUNT))
Tipp: Die Relationen NATION und REGION tauchen jeweils zweimal in der FROM-Klausel auf.
Auszug aus der Abfrage SELECT * FROM REGION TURNOVER: [25]
SUPP REGION
------------AFRICA
AMERICA
ASIA
...
CUST REGION
TURNOVER
------------- -----------------AFRICA
339126360,6419
AFRICA
569002546,2024
AFRICA
141959877,9143
3. Aufgabe: Datentypen
1. Erstellen Sie für die beiden Währungen Euro und Dollar die nutzerdefinierten Datentypen T EUR und T USD vom Quelldatentyp DECIMAL (6,2)! Erstellen Sie eine Tabelle
COLLECTION, bestehend aus den Spalten ITEM vom Typ VARCHAR(20) und PRICE vom
Typ T EUR. Füllen Sie die Tabelle mit den folgenden Testdaten:
ITEM
-------------------NIKON COOLPIX S9
ACER TravelMate 2428
SAMSUNG R40-T2300
CANON CanoScan 4200
BROTHER HL 2030
PRICE
-------158.00
648.00
1020.00
78.60
113.50
Schreiben Sie eine SQL-Abfrage, welche die obigen Waren mit in Dollar ausgezeichneten
Preisen (vom Typ T USD) ausgibt! Gehen Sie dabei von einem Umrechnungskurs von
1:1.20 aus!
Geben Sie alle CREATE, INSERT und SELECT-Anweisungen in ihrer Lösung an!
2. Was ist der jeweilige Datentyp folgender Ausdrücke:
(a) 16 / 0.5
(b) 16 * 2
(c) 1/3
(d) CAST(1 AS DOUBLE)*CAST(2 AS REAL)*CAST(3 AS INTEGER)
(e) CAST(’1’ AS CHAR)
(f) CAST(’1’ AS INTEGER)
2
Professur für Datenbanken
Fakultät Informatik
Institut für Systemarchitektur
(g) CAST(’A’ AS CHAR)
(h) CAST(’A’ AS INTEGER)
(i) CAST(’TEST’ AS CHAR)
4. Aufgabe: Datenmanipulation auf der SAMPLE-Datenbank
Um eine Tabelle mit dem Schema einer bereits vorhandenen Tabelle zu erstellen, kann das
Schlüsselwort LIKE verwendet werden:
CREATE TABLE <new-table> LIKE <old-table>
Die neue Tabelle besitzt dieselben Spaltenbezeichnungen und -definitionen wie die Quelltabelle, jedoch keine Daten.
1. Verbinden Sie sich mit der SAMPLE-Datenbank! Erstellen Sie eine leere Tabelle
MYEMPLOYEE mit dem Schema von EMPLOYEE!
2. Füllen Sie die Tabelle MYEMPLOYEE mithilfe eines einzigen SQL-Befehls, indem Sie alle
Angestellten mit dem Job ’MANAGER’ aus der Tabelle EMPLOYEE in MYEMPLOYEE einfügen!
Hinweis: Informieren Sie sich gegebenenfalls in der DB2-Referenz, wie Daten aus einer
Tabelle in eine andere übernehmen werden können (Stichwort: INSERT-Anweisung).
3. Sie sind Mitarbeiter der Personalabteilung und sollen für folgende Angestellte die
Gehälter ändern:
THEODORE SPENSER: 95000,00
JOHN GEYER: 78000,00
MICHAEL THOMPSON: 82000,00
IRVING STERN: 84000,00
Ändern Sie die betreffenden Daten in der Tabelle MYEMPLOYEE!
4. Der Betriebsrat hat sein Veto eingelegt: Mitarbeitergehälter dürfen sich nicht vermindern. Vereinigen Sie die beiden Relationen EMPLOYEE und MYEMPLOYEE in MYEMPLOYEE, so
dass die fehlenden Mitarbeiter hinzugefügt werden. Bei bereits vorhandenen Mitarbeitern soll das höhere von beiden Gehältern übernommen werden. Führen Sie das Szenario
einmal mit MERGE und einmal mit jeweils genau einer INSERT- und UPDATE-Anweisung
durch.
5. Lassen Sie sich mithilfe einer Mengenoperation diejenigen Manager anzeigen, deren
Gehalt sich abschließend verändert hat! [2]
Abgabe
Bitte senden Sie alle Lösungen in Form eines einzigen SQL-Skripts bis zum Montag, den
22.04.2013, an Martin Kettel ([email protected]). Für Lösungen von
Textaufgaben bitte Kommentare innerhalb des SQL-Skripts einfügen.
3
Professur für Datenbanken
Fakultät Informatik
Institut für Systemarchitektur
Das Lesen der Tutorials ist eine wesentliche Voraussetzung zur erfolgreichen Absolvierung der
Zertifizierung! Wir empfehlen daher, schrittweise während des Semesters diese entsprechend
der aktuellen Vorlesungsthemen durchzugehen.
4