LoesungBlatt11

Übung zur Vorlesung
”Einführung in die Informatik für Hörer anderer Fachrichtungen (WZW)”
IN8003, SS 2010
Prof. Dr. J. Schlichter
Dr. Georg Groh, Dipl.Inform. Dipl.Geogr. Jan Herrmann, Felix Hammerl, BSc
http://www11.in.tum.de/Veranstaltungen/EinfuehrungInformatikWzwSS10/uebung
Lösung zu Blatt 11
• Empfohlender Bearbeitungszeitraum: 12.07 - 16.07
• 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.
Stoff
Es wird empfohlen folgendes Material anzusehen:
• Folien zur Zentralübung Sitzung 9+10+11
• A. Kemper , A. Eickler; Datenbanksysteme - Eine Einführung. Oldenbourg Verlag, 2009.
7. Auflage.
Aufgabe 1 ***
1. Gegeben sei ein erweitertes Universitätsschema mit der folgenden StudentenGF -Relation:
StudentenGF : {[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 anz ) und die Anzahl der weiblichen Studenten pro Fakultät (temporäre Tabelle
anzw ). 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. Durch ein union kann dies jedoch leicht
erreicht werden.
2. Geben Sie für Aufgabe 1.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
float) from StudentenGF group by FakName
Aufgabe 2 **
Formulieren Sie entsprechende SQL-Anfragen auf dem Universitätsschema.
1. Berechnen Sie mit Hilfe einer SQL-Anfrage 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;
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 Mengendifferenz 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: Mengendifferenz wird in SQL durch except ausgedrückt. Manche SQL-Dialekte
weichen davon ab. So wird obige Anfrage in Oracle beispielsweise mittels minus formuliert.
Aufgabe 3 **
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 <= all je ein Beispiel und eine passende Umformulierung an.
Bei numerischen Argumenten können Anfragen mit all in äquivalente Anfragen ohne die
Verwendung von all umgeformt werden. Geben Sie zu den zwei Vergleichsoperationen >=
all und <= all je ein Beispiel und eine passende Umformulierung an.
Beispiel für >=all: Finde die Studenten mit den längsten Studienzeiten.
select Name from Studenten where Semester >= all(select Semester from Studenten);
Äquivalente Formulierung ohne all:
select Name from Studenten where Semester >= (select max(Semester) from Studenten);
Beispiel für <=all: Finde die Studenten mit den kürzesten Studienzeiten.
select Name from Studenten where Semester <= all(select Semester from Studenten);
Äquivalente Formulierung ohne all:
select Name from Studenten where Semester <= (select min(Semester) from Studenten);
2. Bestimmen Sie unter Verwendung von all die Vorlesung, die am häufigsten 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);