Lösung Aufgabenblatt 04

Übung zur Vorlesung
"Einführung in die Informatik für Hörer anderer Fachrichtungen (WZW)"
IN8003, SS 2011
Prof. Dr. J. Schlichter
Dr. Georg Groh, Dipl.Inform. Dipl.Geogr. Jan Herrmann, Florian Schulze BSc., Andrea
Koelle, Thomas Parsch
http://www11.in.tum.de/Veranstaltungen/EinfuehrungInformatikWzwSS11/uebung
Lösung zu Blatt 4
•
Die Aufgaben können selbständig aber auch gut in der Gruppe bearbeitet werden. Sie sind
nicht abzugeben. Es werden detaillierte Musterlösungen bereit gestellt. Wenn Fragen zu
Ihrer Lösung auch nach Vergleich mit der Musterlösung bleiben, können Sie Ihre Fragen
im Forum posten.
•
Zur groben Orientierung ist zu jeder Aufgabe eine Einschätzung des Schwierigkeitsgrades
bzw. Aufwandes angegeben:
* Eher einfach und straightforward
** Schon schwierig oder zumindest aufwändig
*** Ziemlich schwierig oder sehr umfangreich. Wenn man diese Aufgabe halbwegs erfolgreich gemeistert hat, kann man ein wenig stolz auf sich sein.
Sto
Es wird empfohlen folgendes Material anzusehen:
•
Folien zur Zentralübung bis Sitzung 5
•
A. Kemper , A. Eickler; Datenbanksysteme - Eine Einführung. Oldenbourg Verlag, 2009.
7. Auage.
Aufgabe 1 **
Formulieren Sie entsprechende SQL-Anfragen auf dem Universitätsschema.
1. Finden Sie die Namen der Studenten, die in keiner Prüfung eine bessere Note als 3.0 hatten.
Der Aufgabenstellung 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);
2. Finden Sie die Studenten mit der gröÿten Semesterzahl unter Verwendung von Aggregatfunktionen.
select * from Studenten where Semester = (select max(Semester) from Studenten);
Aufgabe 2 *
Suchen Sie unter Verwendung von exists die Professoren heraus, die Vorlesungen halten. Finden
Sie eine weitere alternative äquivalente Formulierungen dieser Anfrage.
Formulierung unter Verwendung von any:
select PersNr, Name from Professoren where PersNr = any (select distinct gelesenVon from
Vorlesungen);
Formulierung unter Verwendung von exists:
select PersNr, Name from Professoren where exists (select * from Vorlesungen where gelesenVon
= PersNr);
Formulierung unter Verwendung von distinct:
select distinct PersNr, Name from Professoren, Vorlesungen where gelesenVon = PersNr;
Aufgabe 3 ***
1. Gegeben sei ein erweitertes Universitätsschema mit der folgenden
StudentenGF
StudentenGF -Relation:
: {[MatrNr : integer, Name : varchar(20), Semester : integer,
Geschlecht
: char, FakName : varchar(20)]}
Ermitteln Sie den Frauenanteil an den verschiedenen Fakultäten in SQL!
Wir berechnen in zwei Unteranfragen die Anzahl der Studenten pro Fakultät (temporäre
Tabelle
anzw ).
anz )
und die Anzahl der weiblichen Studenten pro Fakultät (temporäre Tabelle
Durch den Join über den Namen der Fakultät erhalten wir die zusammengehörigen
Anzahlen aller Studenten und der weiblichen Studenten für die jeweilige Fakultät.
select anz.FakName, anz.AnzStudenten, anzw.AnzWeiblich, cast (anzw.AnzWeiblich * 100.0
/ anz.AnzStudenten as decimal(5,2)) as ProzentWeiblich from (select s.FakName, count(*)
as AnzStudenten from StudentenGF s group by s.FakName) as anz, (select sw.FakName,
count(*) as AnzWeiblich from StudentenGF sw where sw.Geschlecht = 'W' group by
sw.FakName) as anzw where anz.FakName = anzw.FakName
Bitte beachten Sie, dass bei dieser Lösung diejenigen Fakultäten, die gar keine weiblichen
Studenten haben, nicht ausgegeben werden.
erreicht werden.
Durch ein union kann dies jedoch leicht
2. Geben Sie für Aufgabe 3.1 eine Lösung unter Verwendung des case-Konstruktes an.
Wir verwenden das case-Konstrukt, um die Anzahl der Frauen an den jeweiligen Fakultäten
zu ermitteln. Den Frauenanteil erhalten wir dann, indem wir die Anzahl der Frauen durch
die Gesamtanzahl der Studenten an der Fakultät teilen.
select FakName, (sum(case when Geschlecht = 'W' then 1 else 0 end)) / cast (count(*) as
oat) from StudentenGF group by FakName
Aufgabe 4 **
Formulieren Sie entsprechende SQL-Anfragen auf dem Universitätsschema.
1. Berechnen Sie mit Hilfe einer SQL-Anfrage den Umfang des Prüfungsstoes 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;
Gemäÿ der Aufgabenstellung sollen nur die Namen der Studenten ermittelt werden. Um
dennoch zu verhindern, dass Studenten mit gleichen Namen jeweils nur als ein Student
betrachtet werden, wurde in die group by-Klausel das Attribut
MatrNr mit aufgenommen.
2. Bestimmen Sie die Titel der Vorlesungen, deren Umfang der durchschnittlichen Vorlesungsdauer entspricht.
select Titel from Vorlesungen where SWS = (select avg(SWS) from Vorlesungen);
oder:
select Titel from Vorlesungen where SWS in (select avg(SWS) from Vorlesungen);
Diese Anfrage ohne in zu formulieren ist deutlich schwieriger. Die Korrelation kann durch
'Mitschleppen' der Prädikate umgesetzt werden.
select v1.Titel from Vorlesungen v1, Vorlesungen v2 group by v1.VorlNr,v1.Titel,v1.SWS,v1.gelesenVon
having v1.SWS = avg(v2.SWS);
3. Bestimmen Sie die Prüfer, die im Durchschnitt eine Prüfungsnote vergeben, die selbst auch
schon mal als Einzelnote vergeben wurde.
select p.PersNr, avg(p.Note) from prüfen p group by p.PersNr having avg(p.Note) in (select
Note from prüfen);
Entsprechende Anfrage ohne in:
select distinct p.PersNr, avg(p.Note) from prüfen p, prüfen p1 group by p.PersNr, p1.Note
having avg(p.Note) = p1.Note;
Anmerkung: Korrelation kann wieder durch 'Mitschleppen' der Prädikate umgesetzt werden.
4. Berechnen Sie die Gesamtzahl der Semesterwochenstunden, die die einzelnen Professoren
erbringen. Dabei sollen auch die Professoren berücksichtigt werden, die keine Vorlesungen
halten.
(select p.PersNr, p.Name, sum(v.SWS) from Professoren p, Vorlesungen v where p.PersNr
= v.gelesenVon group by p.PersNr, p.Name) union (select p.PersNr, p.Name, 0 from Professoren p where not exists (select * from Vorlesungen where gelesenVon = p.PersNr));
5. Alle Studenten müssen ab sofort alle Vorlesungen von Sokrates hören.
Formulieren Sie
einen SQL-Befehl, der diese Operation durchführt.
Für jeden Studenten müssen in die Relation
hören
Einträge für die Vorlesungen von
Sokrates, die er oder sie noch nicht besucht, eingetragen werden. In SQL kann dies wie
folgt formuliert werden:
insert into hören (select s.MatrNr, v.VorlNr from Studenten s, Vorlesungen v, Professoren
p where p.Name = 'Sokrates' and p.PersNr = v.gelesenVon and (s.MatrNr, v.VorlNr) not
in (select * from hören));
Eine alternative Formulierung mit not exists ist:
insert into hören (MatrNr, VorlNr) (select s.MatrNr, v.VorlNr from Studenten s, Vorlesungen v, Professoren p where p.Name = 'Sokrates' and p.PersNr = v.gelesenVon and not
exists (select * from hören h where h.MatrNr = s.MatrNr and h.VorlNr = v.VorlNr));
Als weitere Alternative kann man im geschachtelten select-Statement eine Mengendierenz
formulieren:
insert into hören (MatrNr, VorlNr) ((select s.MatrNr, v.VorlNr from Studenten s, Vorlesungen v, Professoren p where p.Name = 'Sokrates' and p.PersNr = v.gelesenVon) except
(select * from hören));
Hinweis: Mengendierenz wird in SQL durch except ausgedrückt. Manche SQL-Dialekte
weichen davon ab. So wird obige Anfrage in Oracle beispielsweise mittels minus formuliert.
Aufgabe 5 **
SQL-Anfragen mit all auf dem Universitätsschema:
1. Bei numerischen Argumenten können Anfragen mitall in äquivalente Anfragen ohne die
Verwendung von all umgeformt werden. Geben Sie zu den zwei Vergleichsoperationen
all und
<=
Beispiel für
>=
all je ein Beispiel und eine passende Umformulierung an.
>=all:
Finde die Studenten mit den längsten Studienzeiten.
select Name from Studenten where Semester
>=
all(select Semester from Studenten);
>=
(select max(Semester) from Studenten);
Äquivalente Formulierung ohne all:
select Name from Studenten where Semester
Beispiel für
<=all:
Finde die Studenten mit den kürzesten Studienzeiten.
select Name from Studenten where Semester
<=
all(select Semester from Studenten);
<=
(select min(Semester) from Studenten);
Äquivalente Formulierung ohne all:
select Name from Studenten where Semester
2. Bestimmen Sie unter Verwendung von all die Vorlesung, die am häugsten gehört wird.
select v.Titel from Vorlesungen v, hoeren h where v.VorlNr = h.VorlNr group by v.Titel
having count(*)
>=
all (select count(*) from hoeren group by VorlNr);