2. ¨Ubung - Datenbankadministration

Professur für Datenbanken
Fakultät Informatik
Institut für Systemarchitektur
2. Übung - Datenbankadministration
”
SQL Basics“
Ziel der Übung. Erweiterung der Kenntnisse der SQL-Anfragesprache.
Aufgaben
Falls Sie den Import der Daten des TPC-H Benchmarks aus Übung 1 nicht vollständig gelöst
haben, verbinden Sie sich zur TPCH-Datenbank und importieren Sie die Daten mittels:
db2 -tf /home/dbadmin/scripts/tpch create.sql
db2 -tf /home/dbadmin/scripts/tpch import clean.sql
1. Aufgabe: SQL-Anfragen – Zulieferer, Teile und Bestellungen (1.5 Punkte)
1. Geben Sie zu jeder Nation (NATION.N NAME) die Anzahl der Zulieferer (’SUPPCOUNT’)
an und sortieren Sie das Ergebnis absteigend nach der Anzahl.
2. Geben Sie zu den Teilen (PART.P NAME), dessen Verpackungsbezeichnungen
(PART.P CONTAINER) den String ’BOX’ enthalten sollen, die Grösse (PART.P SIZE) an.
Geben Sie ausserdem an, ob sich die Grösse zwischen 10 und 20 (’OK’) oder ausserhalb
dieses Bereiches (’out of range’) liegt. Sortieren Sie die Ausgaben der Grösse nach
aufsteigend.
Tipp: Verwenden Sie BETWEEN und die CASE-Anweisung.
3. Geben Sie zu jedem Teil (PART.P NAME) an, wie oft es bestellt wurde
(LINEITEM.L QUANTITY). Sortieren Sie das Ergebnis absteigend nach der Anzahl der
Bestellungen, bei gleicher Anzahl alphabetisch ansteigend nach Teil-Bezeichnung.
Tipp: Verwenden Sie einen OUTER JOIN und die CASE-Anweisung für das Eingliedern
nicht bestellter Teile.
2. Aufgabe: Erstellen einer Sicht (Umsatz nach Regionen) (2.5 Punkte)
Erstellen Sie eine Sicht REGION TURNOVER, in welcher der Umsatz pro Region des Zulieferers
(REGION.R NAME) und Region des Kunden berechnet wird. Der Umsatz berechnet sich wie
folgt:
TURNOVER = SUM(LINEITEM.L EXTENDEDPRICE*(1-LINEITEM.L DISCOUNT))
Tipp: Die Relationen NATION und REGION tauchen jeweils zweimal in der FROM-Klausel auf.
3. Aufgabe: Abfrage einer Sicht (1 Punkt)
Ermitteln Sie mithilfe der in Aufgabe 2 erstellten Sicht die Zulieferregionen, welche mindestens
einen Gesamtumsatz von 100 Mio haben.
1
Professur für Datenbanken
Fakultät Informatik
Institut für Systemarchitektur
4. Aufgabe: Mehrfachgruppierung (2.5 Punkte)
Berechnen Sie unter Zuhilfenahme der Sicht aus Aufgabe 2 sowohl den Umsatz pro Zuliefererund Kundenregion, als auch den Umsatz pro Zulieferregion, den Umsatz pro Kundenregion
und den Gesamtumsatz. Attribute, welche nicht zur Gruppierung verwendet werden, sollen
NULL-Werte enthalten. Sortieren Sie das Ergebnis aufsteigend nach Zulieferer- und Kundenregion. Eine Teilmenge des Ergebnisses ist im Folgenden angegeben:
SUPP_REGION
-----------------[...]
AFRICA
[...]
AFRICA
[...]
[...]
-
CUST_REGION
TURNOVER
------------------ --------------------AMERICA
60902398,7262
-
540138536,1061
AMERICA
195992000,8475
-
1701734578,2403
Verwenden Sie die UNION-Klausel. Sie können typisierte Nullwerte innerhalb der SELECTKlausel angeben (CAST(NULL AS CHAR)).
5. Aufgabe: Datentypen (2.5 Punkte)
Erstellen Sie für die beiden Währungen Euro und Dollar die benutzerdefinierten 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!
6. Aufgabe: Zertifizierungsvorbereitung (Bonusaufgabe, 1 Punkt)
Die hier gestellten Aufgaben sollen Sie auf die IBM-DB2-Zertifizierung vorbereiten.
1. Given the following statement:
SELECT hyear, AVG(salary)
FROM SELECT YEAR(hiredate) AS hyear, salary
FROM employee WHERE salary > 30000
GROUP BY hyear
Which of the following describes the result if this statement is executed?
2
Professur für Datenbanken
Fakultät Informatik
Institut für Systemarchitektur
A. The statement will return the year and average salary for all employees that have a
salary greater than $ 30,000, sorted by year.
B. The statement will return the year and average salary for all employees hired within
a given year that have a salary greater than $ 30,000.
C. The statement will return the year and average salary for all years that every employee hired had a salary greater than $ 30,000.
D. The statement will return the year and average salary for all years that any employee
had a salary greater than $ 30,000.
2. Which two of the following statements are true about the HAVING clause?
A. The HAVING clause is used in place of the WHERE clause.
B. The HAVING clause uses the same syntax as the WHERE clause.
C. The HAVING clause can only be used with the GROUP BY clause.
D. The HAVING clause accepts wildcards.
E. The HAVING clause uses the same syntax as the IN clause.
3. Given the following table definition:
SALES
---------------------------SALES_DATE DATE
SALES_PERSON CHAR(20)
REGION CHAR(20)
SALES INTEGER
Which of the following SQL statements will remove all rows that had a SALES DATE in
the year 1995?
A. DELETE * FROM sales WHERE YEAR(sales date)=1995
B. DELETE FROM sales WHERE YEAR(sales date)=1995
C. DROP * FROM sales WHERE YEAR(sales date)=1995
D. DROP FROM sales WHERE YEAR(sales date)=1995
Abgabe
Bitte senden Sie alle Lösungen in Form eines einzigen SQL-Skripts bis zum Sonntag, den
01.11.2015, an Martin Kettel ([email protected]). Für Lösungen von
Textaufgaben bitte Kommentare innerhalb des SQL-Skripts einfügen.
Bitte Namen und Instanz (db2insXX) als Kommentar mit einfügen.
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.
3