Übungsblatt 5 - Lösung - Informatik

Universität Augsburg, Institut für Informatik
Prof. Dr. W. Kießling
Dr. A. Huhn, F. Wenzel, M. Endres
WS 2009/2010
27. Nov. 2009
Lösungsblatt 5
Datenbanksysteme I
Hinweis: Die in diesem Lösungsblatt vorgestellten SQL-Queries sind alle auf das Sporting Goods Schema anwendbar.
Bei der Ausführung der folgenden Musterlösungen ist jedoch zu beachten, dass die kursiven einfachen Anführungszeichen
durch die entsprechenden von SQL gewünschten einfachen Anführungszeichen ersetzt werden müssen.
Aufgabe 1: SQL-Queries
Lösungen:
a) Name aller Angestellten, die in Abteilung 41 arbeiten, alphabetisch sortiert nach Nachname:
SELECT last_name, first_name
FROM s_emp
WHERE dept_id=’41’
ORDER BY last_name, first_name;
b) Nachname und Vorname aller Angestellten samt Name der Abteilung, in der sie arbeiten sowie der dazugehörigen
Region:
SELECT s_emp.last_name, s_emp.first_name,
s_dept.name AS "DEP NAME", s_region.name AS "REGION"
FROM s_emp, s_dept, s_region
WHERE s_emp.dept_id = s_dept.id AND s_dept.region_id = s_region.id;
c) Namen aller Angestellten sowie deren dazugehöriger Manager:
SELECT e.last_name AS "Manager name", e.first_name AS "MANAGER FIRST NAME" ,
m.last_name , m.first_name
FROM s_emp e, s_emp m
WHERE e.id = m.manager_id;
d) Name aller Kunden, die noch keinem Kundenbetreuer zugeordnet wurden:
SELECT s_customer.name
FROM s_customer
WHERE s_customer.id NOT IN
(SELECT c.id
FROM s_customer c , s_emp e
WHERE c.sales_rep_id = e.id);
e) Liste aller Kunden mit GOOD oder EXCELLENT Kredit-Rating:
SELECT name
FROM s_customer
WHERE credit_rating=’GOOD’ OR credit_rating=’EXCELLENT’;
f) Daten aller Lager samt zugehöriger Manager plus Angestellte, die kein Manager eines Lagers sind:
SELECT w.id, w.address , w.city, w.state, w.country ,
e.last_name, e.first_name
FROM s_warehouse w RIGHT OUTER JOIN s_emp e
ON w.manager_id = e.id;
1
g) Namen und ID aller Mitarbeiter, deren Kommision unter 12 Prozent liegt:
SELECT e.id, e.last_name, e.first_name FROM s_emp e WHERE e.commission_pct<12;
Hier zeigt sich das Problem von Queries mit potentiellen NULL-Werten, da im Ergebnis nur Tupel vorkommen,
für die die im WHERE-Teil der Query formulierte Bedingung true ergibt. Für NULL-Werte in der Spalte COMMISSION PCT ergibt die Bedingung aber nach Seite 62 im Skript unknown. Somit liefert die Query keine Tupel
zurück, für die keine Kommission angegeben ist.
Aufgabe 2: Count-Bug
Lösungen:
a) Relation erstellen:
CREATE TABLE R (
dept_nr INTEGER,
emp_nr INTEGER,
job varchar(50),
PRIMARY KEY (dept_nr, emp_nr));
b) Tupel einfügen:
INSERT
INSERT
INSERT
INSERT
INSERT
INTO
INTO
INTO
INTO
INTO
R
R
R
R
R
VALUES(1,1,’programmer’);
VALUES(1,2,’clerk’);
VALUES(2,3,’clerk’);
VALUES(2,4,’clerk’);
VALUES(3,5,’sales’);
c) Der erste intuitive Ansatz
SELECT dept_nr
FROM emp
WHERE job = ’clerk’
GROUP BY dept_nr
HAVING COUNT(*) <= 2
schlägt fehl. In der WHERE-Klausel werden alle Einträge außer denen mit job = ’clerk’ eliminiert. Dadurch fallen
sämtliche Einträge zum Department 3 weg. Das Department 3 fehlt also auch im Ergebnis, obwohl es rein intuitiv
auch gesucht war.
Ein anderer Lösungsansatz
SELECT
FROM
GROUP
HAVING
dept#
R
BY dept#
COUNT(*) <= 2 AND job = ’clerk’;
ist syntaktisch falsch, da Nicht-Gruppierungsattribute wie job nur in Form von Aggregationsausdrücken in einer
HAVING-Klausel vorkommen dürfen.
Korrekte SQL-Anfrage:
(SELECT dept_nr FROM R)
MINUS
(SELECT dept_nr FROM R
WHERE job = ’clerk’
GROUP BY dept_nr
HAVING COUNT(*) > 2);
2
d) Ein ähnliches Phänomen lässt sich in der s inventory Relation beobachten. Gegeben sei dazu folgende Query:
SELECT warehouse_id
FROM s_inventory
WHERE product_id =’10013’ AND amount_in_stock <1
GROUP BY warehouse_id;
Gesucht werden hier alle Lager, in denen der Artikel mit Nummer 10013 nicht vorrätig ist, also auch diejenigen,
die den entsprechenden Artikel nicht führen. Die Query gibt jedoch ein leeres Ergebnis zurück, da Lager 101
noch 400 Exemplare des Artikels vorrätig hat, Lager 10501 noch 314. Dabei bleiben Lager 201, 301 und 401
unberücksichtigt, da diese den Artikel generell nicht im Sortiment haben. Laut Verbalisierung der gewünschten
Query sollten aber auch diese Lager im Ergebnis enthalten sein.
Dieses Beispiel verdeutlicht nochmals, dass der sogenannte Count-Bug nicht ein inhärentes Problem der CountFunktion ist, sondern vielmehr ein generelles Problem von SQL darstellt. Analog zu c) liefert folgende Query das
gewollte Ergebnis:
(SELECT warehouse_id FROM s_inventory)
MINUS
(SELECT warehouse_id FROM s_inventory
WHERE product_id=’10013’AND
amount_in_stock>1
GROUP BY warehouse_id);
Aufgabe 3: Aggregation und arithmetische Ausdrücke
Lösungen:
a) Durchschnittsgehalt der Firma:
SELECT AVG(salary) from s_emp;
b) Anzahl der Kunden pro Region:
SELECT r.name, count(DISTINCT c.id) AS "Number of Customers"
FROM s_customer c, s_region r
WHERE c.region_id = r.id
GROUP BY r.name;
c) Durchschnittsbestellbetrag und Name aller Kunden mit mehr als zwei Bestellungen:
SELECT c.id, c.name, AVG(o.total) AS "Durchschnittsbetrag"
FROM s_ord o, s_customer c
WHERE o.customer_id = c.id
GROUP BY c.id, c.name
HAVING count(DISTINCT o.id)>=2;
d) Der aktuelle Lagerbestand wird um 100 erhöht um anschließend halbiert, um den gewünschten Wert zu erreichen.
Zusätzlich wird die Differenz zwischen dem so vorherberechneten Ist-Wert und dem geforderten Soll-Wert (definbiert durch Reorder Point) ermittelt, um festzustellen, wo bei dieser Kalkulation Engpässe auftreten werden. Die
entsprechende Query lautet:
SELECT product_id, amount_in_stock , (amount_in_stock + 100)/2,
reorder_point, ((amount_in_stock + 100)/2 - reorder_point) AS "DIFFERENCE"
FROM s_inventory
WHERE warehouse_id=’201’ AND (product_id=’20106’ OR product_id=’20108’);
3
e) Monatliche Ausgaben bezüglich Gehalt für die einzelnen Zweige Administration, Finance, Operations und Sales:
SELECT d.name, sum(e.salary) AS "Monthly Salary Costs"
FROM s_dept d, s_emp e
WHERE e.dept_id = d.id
GROUP BY d.name
ORDER BY d.name;
f) Daten aller Bestellungen, die innerhalb von 10 Tagen nach Bestelleingang verschickt wurden:
SELECT * FROM s_ord WHERE date_shipped - date_ordered < 10;
4