05loesung - Institut für Informatik

Universität Augsburg, Institut für Informatik
Prof. Dr. W. Kießling
F. Wenzel, L. Rudenko
WS 2014/2015
21. Nov. 2014
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: Aggregatsfunktionen, Self-Joins und NULL-Werte
Lösungsvorschläge:
a) Bei der Ansicht von s emp wird deutlich, dass nur für vier Mitarbeiter ein Wert für commission pct definiert ist,
für alle anderen Mitarbeiter ist dieser Wert NULL. Die Aggregatsfunktion AVG() berechnet den Durchschnitt aller
vorhandenen Werte, NULL-Werte werden dabei also nicht berücksichtigt:
SELECT DISTINCT AVG(commission_pct)
FROM s_emp;
Diese Anfrage liefert als Ergebnis 12.5 zurück, da dies der korrekte Durchschnitt der 4 eingetragenen KommissionsWerte ist. Will man allerdings den Durchschnitt über alle Mitarbeiter bilden, einen NULL-Wert also als 0 interpretieren, so muss der Durchschnitt selbst gebildet werden:
SELECT DISTINCT
(SELECT DISTINCT sum(commission_pct)
FROM s_emp) /
(SELECT DISTINCT count(*)
FROM s_emp) Durchschnitt
FROM s_emp;
Diese Anfrage liefert entsprechend den Wert 2 als Durchschnitt zurück.
b) Die Manager unter den Mitarbeitern lassen sich durch einen Selbst-Join bestimmen:
SELECT DISTINCT m.*
FROM s_emp e, s_emp m
WHERE e.manager_id = m.id;
c) Anfrage mit Subquery:
SELECT DISTINCT id
FROM s_emp
WHERE id NOT IN
(SELECT DISTINCT manager_id
FROM s_emp
WHERE manager_id IS NOT NULL);
Der Zusatz IS NOT NULL in der Subquery ist entscheidend, da die Subquery sonst NULL in der Ergebnismenge
enthält. Ein Vergleich mit NULL liefert immer unknown zurück, somit wäre das Gesamtergebnis der Haupt-Query
leer.
Anfrage mit MINUS:
(SELECT DISTINCT id
FROM s_emp)
MINUS
(SELECT DISTINCT manager_id
FROM s_emp);
1
d) Um die geforderte Anzahl zu ermitteln ist eine einfache Anfrage offensichtlich:
SELECT DISTINCT count(*)
FROM s_emp
WHERE commission_pct <= 12.5;
Auch hier stellt sich wieder die Frage, wie NULL-Werte interpretiert werden sollen. Soll NULL als 0 gesehen
werden, so ist eine erweiterte Anfrage notwendig:
SELECT DISTINCT count(*)
FROM s_emp WHERE id NOT IN
(SELECT DISTINCT id
FROM s_emp
WHERE commission_pct > 12.5);
Aufgabe 2: Aggregation und arithmetische Ausdrücke
Lösungsvorschläge:
a) Durchschnittsgehalt der Firma:
SELECT AVG(salary)
FROM s_emp;
b) Anzahl der Kunden pro Region:
SELECT r.name, COUNT(DISTINCT c.id) AS KundenAnzahl
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) Daten aller Bestellungen, die innerhalb von 10 Tagen nach Bestelleingang verschickt wurden:
SELECT *
FROM s_ord
WHERE date_shipped - date_ordered < 10;
Aufgabe 3: Datendefinition und Integritätsbedingungen
Lösungsvorschläge:
a) Erstellen der gewünschten Relation:
CREATE TABLE programmer
(EmpNo VARCHAR(3),
Last_Name VARCHAR(25) NOT NULL,
First_Name VARCHAR(25),
Hire_Date Date,
Project NUMBER(3),
Language VARCHAR(15),
TaskNo NUMBER(2),
Clearance VARCHAR(25),
CONSTRAINT programmer_pk PRIMARY KEY(EmpNo)
);
2
b) Die project Relation wird folgendermaßen erstellt:
CREATE TABLE project
(project_id NUMBER(3),
project_name VARCHAR(50),
CONSTRAINT project_pk PRIMARY KEY(project_id)
);
c) Anschließend wird ein Foreign-Key in der programmer Relation hinzugefügt:
ALTER TABLE programmer ADD CONSTRAINT programmer_fk FOREIGN KEY(Project)
REFERENCES project(project_id) ON DELETE CASCADE;
Wird ein Projekt Tupel aus project gelöscht, so werden über die Foreign-Key Bedingung ON DELETE CASCADE
auch verbundene Tupel in programmer gelöscht.
d) Erweitern der project Relation:
ALTER TABLE project ADD(manager_id INTEGER);
Dezimieren der programmer Relation:
ALTER TABLE programmer DROP(clearance);
e) Einfügen von Tupeln in die project Relation:
INSERT INTO project VALUES(1, ’projekt1’, 22);
INSERT INTO project VALUES(2, ’projekt2’, 21);
INSERT INTO project VALUES(3, ’projekt3’, 22);
Einfügen von Tupeln in die programmer Relation:
INSERT INTO programmer VALUES(’1’, ’Maier’, ’Hans’,
TO_DATE(’04-03-1983’, ’MM-DD-YYYY’), 1, ’Java’, 44);
INSERT INTO programmer VALUES(’2’, ’Huber’, ’Paul’,
TO_DATE(’11-23-2004’, ’MM-DD-YYYY’), 2, ’C++’, 43);
Löschen des Eintrags von Projekt 1:
DELETE FROM project WHERE project_id = 1;
Beim Löschen von Projekt 1 werden alle Programmierer aus programmer gelöscht, die über den Fremdschlüssel
mit Projekt 1 verbunden sind. Dies war in Teilaufgabe c) bereits explizit so vorgesehen.
Aufgabe 4: Deklarative Änderungsoperationen
Lösungsvorschläge:
a) Erstellen der s candidates Relation:
CREATE TABLE s_candidates(
id VARCHAR2(3) PRIMARY KEY,
last_name VARCHAR2(20),
first_name VARCHAR2(20),
salary NUMBER (11,2));
b) Einfügen aller Mitarbeiter aus s emp, die nicht bereits Manager in der Hauptgesellschaft sind:
INSERT INTO s_candidates
SELECT e.id, e.last_name, e.first_name, e.salary
FROM s_emp e
WHERE e.id NOT IN
(SELECT f.id FROM s_emp f, s_emp m WHERE f.id= m.manager_id);
3
c) Gehaltserhöhung für alle Kandidaten, die bereits Kunden in der Hauptgesellschaft betreuten:
UPDATE s_candidates
SET salary = 1.2*salary
WHERE id IN
(SELECT e.id FROM s_emp e, s_customer c WHERE c.sales_rep_id=e.id);
d) Löschen aller Kandidaten, deren Gehalt 30 Prozent über dem Durchschnittsgehalt in s candidates liegt:
DELETE FROM s_candidates
WHERE salary >(SELECT 1.3*AVG(salary) FROM s_candidates);
e) Erstellen einer View mit Nachnamen und Vornamen der Kandidaten:
CREATE VIEW cand_list AS
(SELECT last_name, first_name
FROM s_candidates);
4