Übungsblatt 5 - Institut für Informatik

Universität Augsburg, Institut für Informatik
Prof. Dr. W. Kießling
Dr. A. Huhn, M. Endres, T. Preisinger
WS 2007/2008
23. Nov. 2007
Lösungsblatt 5
Datenbanksysteme I
Aufgabe 1: SQL Queries
Anfragen in SQL.
1. Finden Sie die Studenten, die mindestens im neunten Semester sind.
SELECT * FROM Studenten WHERE Semester >= 9;
2. Finden Sie pro Vorlesung die jeweiligen Hörer. Geben Sie Matrikelnummer und Name des Studierenden sowie
Vorlesungsnummer und Titel der Vorlesung aus.
SELECT s.MatrNr, s.Name, v.VorlNr, v.Titel
FROM Studenten s, hören h, Vorlesungen v
WHERE s.MatrNr = h.MatrNr and h.VorlNr = V.VorlNr;
3. Finden Sie die Studierenden mit der größten Semesterzahl.
SELECT * FROM Studenten
WHERE Semester = (SELECT MAX(Semester) FROM Studenten);
4. Berechnen Sie den Umfang des Prüfungsstoffes jedes Studierenden. Es soll der Name des Studierenden und die
Summe der Semesterwochenstunden der Prüfungsvorlesungen ausgegeben werden.
SELECT s.Name, SUM(v.SWS)
FROM Studenten s, hören h, Vorlesungen v
WHERE s.MatrNr = h.MatrNr and h.VorlNr = V.VorlNr
GROUP BY s.Name;
5. Finden Sie die Namen der Studierenden, die in keiner Prüfung eine bessere Note als 3,0 hatten. Die Anfrage
SELECT s.Name
FROM Studenten s, prüfen p
WHERE s.MatrNr = p.MatrNr
AND p.Note > 3;
liefert nur Studenten, die auch schon ein Prüfung abgeschlossen haben. Es sind aber alle Studenten gesucht, die in
keiner Prüfung eine bessere Note als 3,0 erzielten. Dies schließt auch die Studenten ein, die noch überhaupt keine
Prüfung abgelegt haben. Eine korrekte Anfrage lautet somit:
SELECT s.Name, s.MatrNr
FROM Studenten s
WHERE NOT EXISTS (SELECT *
FROM prüfen p
WHERE p.MatrNr = s.MatrNr
AND p.Note < 3.0);
1
Aufgabe 2: Aggregation und Gruppierung in SQL
Erstellen der Relation Produkte:
CREATE TABLE Produkte (
ID INTEGER Primary Key,
Anzahl_Lager INTEGER,
Anzahl_Shops INTEGER);
insert
insert
insert
insert
insert
insert
insert
insert
insert
into
into
into
into
into
into
into
into
into
Produkte
Produkte
Produkte
Produkte
Produkte
Produkte
Produkte
Produkte
Produkte
Values
Values
Values
Values
Values
Values
Values
Values
Values
(1,
(2,
(3,
(4,
(5,
(6,
(7,
(8,
(9,
1, 1);
1, 2);
2, 1);
2, 2);
NULL, 1);
NULL, 2);
1, NULL);
2, NULL);
NULL, NULL);
a) Anfrage:
SELECT Anzahl_Lager,
SUM(Anzahl_Shops) as a_sum,
COUNT(*) as a_count,
COUNT(DISTINCT Anzahl_Shops) as a_d_count,
MAX(Anzahl_Shops) as a_max,
MIN(Anzahl_Shops) as a_min,
AVG(Anzahl_Shops) as a_avg
FROM Produkte
GROUP BY Anzahl_Lager;
ANZAHL LAGER
1
2
NULL
A SUM
3
3
3
A COUNT
3
3
3
A D COUNT
2
2
2
A MAX
2
2
2
A MIN
1
1
1
A AVG
1,5
1,5
1,5
• Im Ergebnis kommt nur eine Gruppe für NULL-Werte vor. Bei Gruppierung werden NULLs also als gleich
betrachtet.
• Die Summe ist die Summe der tatsächlichen Werte für Anzahl Shops. NULL hat hier keine Auswirkungen.
• COUNT(*) zählt alle Tupel, auch das mit Anzahl Shops = NULL. Im Gegensatz dazu zählt COUNT(DISTINCT
Anzahl Shops) Einträge mit Anzahl Shops = NULL nicht.
• MAX(Anzahl Shops) und MIN(Anzahl Shops) liefern das Maximum bzw. das Minimum ohne NULLs zu
berücksichtigen, obwohl NULL beim Sortieren von Oracle als größter Wert angesehen wird.
• AVG(Anzahl Shops) liefert den Duchschnitt der Tupelwerte ungleich NULL. NULL-Werte beeinflussen den
Durchschnitt also nicht.
2
b) Anfrage:
SELECT *
FROM Produkte P1, Produkte P2
WHERE P1.Anzahl_Lager = P2.Anzahl_Lager;
Ergebnis:
ID
7
2
1
7
2
1
8
4
3
8
4
3
7
2
1
8
4
3
ANZAHL LAGER
1
1
1
1
1
1
2
2
2
2
2
2
1
1
1
2
2
2
ANZAHL SHOPS
NULL
2
1
NULL
2
1
NULL
2
1
NULL
2
1
NULL
2
1
NULL
2
1
ID
1
1
1
2
2
2
3
3
3
4
4
4
7
7
7
8
8
8
ANZAHL LAGER
1
1
1
1
1
1
2
2
2
2
2
2
1
1
1
2
2
2
ANZAHL SHOPS
1
1
1
2
2
2
1
1
1
2
2
2
NULL
NULL
NULL
NULL
NULL
NULL
Tupel mit P1.Anzahl Lager = NULL bzw. P2.Anzahl Lager = NULL fehlen. Das bedeutet NULLs werden hier als
unterschiedliche Werte betrachtet (NULL <> NULL).
Aufgabe 3: Count-Bug
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’);
3
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
MINUS
(SELECT
WHERE
GROUP
HAVING
dept_nr FROM R)
dept_nr FROM R
job = ’clerk’
BY dept_nr
COUNT(*) > 2);
4