Datenbanksysteme I - Institut für Informatik

Universität Augsburg, Institut für Informatik
Prof. Dr. W. Kießling
L. Rudenko, Dr. F. Wenzel
WS 2016/2017
25. Nov. 2016
Lösungsblatt 5
Datenbanksysteme I
Aufgabe 1: SQL – Teil 1 (Hausaufgabe)
a) Finden Sie alle Studenten (MatrNr und Name), die im 2. Semester sind.
SELECT MatrNr, Name
FROM Studenten
WHERE Semester = 2;
b) Finden Sie Titel alles Vorlesungen, die 3 ODER 4 SWS in Anspruch nehmen.
SELECT Titel
FROM Vorlesungen
WHERE SWS = 3 OR SWS = 4;
c) Finden Sie PersNr eines Assistenten, der einen Boss mit der Nummer 2125 hat UND im Fachgebiet ’Ideenlehre’ arbeitet.
SELECT PersNr
FROM Assistenten
WHERE Boss = 2125 AND Fachgebiet = ’Ideenlehre’;
Aufgabe 2: SQL – Teil 2 (Hausaufgabe)
a) Welche Assistenten gehören zu welchem Professor?
SELECT Professoren.Name, Assistenten.Name
FROM Professoren, Assistenten
WHERE Professoren.PersNr = Assistenten.Boss;
b) Finden Sie den Namen eines Professors, der die Vorlesung mit dem Titel ’Wissenschaftstheorie’
hält?
SELECT Name
FROM Professoren, Vorlesungen
WHERE PersNr = gelesenVon AND Titel = ’Wissenschaftstheorie’;
c) Vorlesungen welcher Professoren (Name) hört Student Carnap?
SELECT DISTINCT p.Name
FROM Studenten s, hören h, Vorlesungen v, Professoren p
WHERE s.MatrNr = h.MatrNr AND h.VorlNr = v.VorlNr
AND v.gelesenVon = p.PersNr AND s.Name = ’Carnap’;
1
d) Finden Sie Namen solcher Studenten, die im 3. bis 6. Semester sind UND NICHT die Note 1
haben.
SELECT s.Name
FROM Studenten
WHERE s.MatrNr
AND s.Semester
AND NOT p.Note
s, prüfen p
= p.MatrNr
BETWEEN 3 AND 6
= 1;
e) Finden Sie die Vorlesungen, deren Umfang einer Vorlesung entspricht, die von Sokrates gehalten wird.
SELECT DISTINCT v1.*
FROM Vorlesungen v1, Vorlesungen v2, Professoren p
WHERE v1.SWS = v2.SWS AND v2.gelesenVon = p.PersNr
AND p.Name = ’Sokrates’;
Aufgabe 3: SQL – Teil 3 (Präsenzaufgabe)
a) Finden Sie alle Studenten deren Semesterzahl zwischen 1 und 4 ist:
SELECT *
FROM Studenten
WHERE Semester BETWEEN 1 AND 4;
b) Geben Sie die Studenten aus, die im ersten oder dritten Semester sind:
SELECT *
FROM Studenten
WHERE Semester IN (1,3);
c) Geben Sie alle Professoren absteigend nach Ihrem Rang und aufsteigend sortiert nach Ihrem
Namen aus:
SELECT PersNr, Name, Rang
FROM Professoren
ORDER BY Rang DESC, Name ASC;
d) Welche Studierenden hören welche Vorlesungen?
SELECT Name, Titel
FROM Studenten, hoeren, Vorlesungen
WHERE Studenten.MatrNr = hoeren.MatrNr
AND hoeren.VorlNr = Vorlesungen.VorlNr;
e) Gesucht sind die Titel der Vorlesungen, deren Umfang der durchschnittlichen Vorlesungsdauer
entspricht:
SELECT Titel
FROM Vorlesungen
WHERE SWS = (SELECT AVG(SWS)
FROM Vorlesungen);
2
bzw.
SELECT v1.Titel
FROM Vorlesungen v1, Vorlesungen v2
GROUP BY v1.VorlNr, v1.Titel, v1.SWS
HAVING v1.SWS = AVG(v2.SWS);
f) Finde Sie die Studierenden mit den kürzesten Studienzeiten:
SELECT Name
FROM Studenten
WHERE Semester = (SELECT MIN(Semester)
FROM Studenten);
g) Suchen Sie unter Verwendung von SOME die Professoren heraus, die Vorlesungen halten.
Finden Sie eine weitere alternative äquivalente Formulierungen dieser Anfrage:
SELECT PersNr, Name
FROM Professoren
WHERE PersNr = SOME (SELECT DISTINCT gelesenVon
FROM Vorlesungen);
und
SELECT DISTINCT PersNr, Name
FROM Professoren, Vorlesungen
WHERE gelesenVon = PersNr;
h) Finden Sie die Namen der Studenten, die in keiner Prüfung eine bessere Note als 3,0 hatten:
Der Aufgabestellung zufolge sind all die Studenten gesucht, die in keiner Prüfung eine bessere
Note als 3,0 erzielten. Dies schließt damit auch die Studenten ein, die noch überhaupt keine
Prüfung abgelegt haben.
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);
i) Berechnen Sie den Umfang des Prüfungsstoffes jedes Studenten. Es sollen der Name des Studenten und die Summe der Semesterwochenstunden der Prüfungsvorlesungen ausgegeben werden:
SELECT s.Name, SUM(v.SWS)
FROM Studenten s, prüfen p, Vorlesungen v
WHERE s.MatrNr = p.MatrNr AND p.VorlNr = v.VorlNr
GROUP BY s.Name, s.MatrNr;
3
Aufgabe 4: SQL-Queries (Präsenzaufgabe)
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 DEPT_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) 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_emp e, s_customer c WHERE e.id = c.sales_rep_id);
d) Liste aller Kunden mit GOOD oder EXCELLENT Kredit-Rating:
SELECT *
FROM s_customer
WHERE credit_rating = ’GOOD’ OR credit_rating = ’EXCELLENT’;
4