Lösung¨Ubungsblatt SQL – Projektdatenbank

Lösung Übungsblatt SQL – Projektdatenbank
Zur Lösung der folgenden Aufgaben können diese beiden Web-Interfaces verwendet
werden:
• Standardmodell:
http://systemanalyse.dyndns.org/cgi-bin/projektdb/index.pl
• Modell mit NULL Werten
http://systemanalyse.dyndns.org/cgi-bin/projektdb-null/index.pl
Gegeben seien folgende Tabellenschemata einer relationalen Datenbank (die
Primärschlüssel sind unterstrichen und die Fremdschlüssel kursiv dargestellt):
• angestellte(angnr, name, wohnort, abtnr)
• projekt(pnr, pname, pfiliale, pleiter)
• angpro(angnr, pnr, prozarbzeit)
• abteilung (abtnr, name, abtleiter)
Lösen Sie die folgenden Aufgaben in Standard-SQL:
(a) Geben Sie alle Angestellten absteigend sortiert nach Wohnort und Namen aus.
select * from angestellte order by wohnort desc, name desc;
(b) Ermitteln sie alle Angestellten (angnr reicht), die zwischen 30 und 50 Prozent
ihrer Arbeitszeit an einem Projekt mitarbeiten.
select angnr from angpro where prozarbzeit between 30 and 50;
(c) Ermitteln Sie alle Abteilungen, deren Name mit ’M’ beginnt oder auf ’ing’
endet.
select * from abteilung where name like ’M%’ or name like
’%ing’;
(d) Ermitteln Sie die Anzahl der Projekte, an denen der Angestellte mir der angnr
2814 mitarbeitet.
select count(*) from angpro where angnr=2814;
(e1) Geben Sie alle Angestellten (angnr reicht) aus, die an genau zwei Projekten
mitarbeiten.
select angnr from angpro group by angnr having count(*)=2;
(e2) Geben Sie die Angestelltennummer und den Namen aller Angestellten aus, die
an genau zwei Projekten mitarbeiten. Sortieren Sie das Ergebnis nach dem
Namen der Angestellten.
select angnr, name from angestellte where angnr in (select angnr
from angpro group by angnr having count(*)=2) order by name;
1
(f) Geben Sie alle Angestellten (Nr, Name und Wohnort) aus, die am Projekt
’p-3’ (ist der PNAME) mitarbeiten.
Mit Natural Join funktioniert es, da es sonst keine gleichnamigen Spalten
gibt:
select angnr, name, wohnort from angestellte natural join angpro
natural join projekt where pname=’p-3’;
Sicherer ist es jedoch, wenn man explizit die Spalten angibt über die gejoint
werden soll:
select angnr, name, wohnort from angestellte join angpro using
(angnr) join projekt using (pnr) where pname=’p-3’;
Diese Variante würde auch funktionieren, wenn die Fremdschlüssel nicht den
selben Namen hätten wie die Attribute, die sie referenzieren:
select angestellte.angnr, name, wohnort from angestellte join
angpro on (angestellte.angnr=angpro.angnr) join projekt on
(angpro.pnr=projekt.pnr) where pname=’p-3’;
Vor SQL-92:
select angestellte.angnr, name, wohnort from angestellte,
angpro, projekt where angestellte.angnr=angpro.angnr and
angpro.pnr=projekt.pnr and pname=’p-3’;
Mit Subquery:
select angnr, name, wohnort from angestellte where angnr in
(select angnr from angpro where pnr in (select pnr from projekt
where pname=’p-3’));
(g) Geben Sie den Namen des Abteilungsleiters der Abteilung ’Marketing’ aus.
select angestellte.name from abteilung join angestellte on
(abtleiter=angnr) where abteilung.name=’Marketing’;
Mit Subquery: select name from angestellte where angnr=(select
abtleiter from abteilung where name=’Marketing’);
(h) Geben Sie die Anzahl der Projekte aus, bei denen noch kein Projektleiter
definiert ist.
select count(*) from projekt where pleiter is null
(i1) Geben Sie alle Angestellten inklusive der Abteilungsdaten, zu der sie gehören,
aus.
Natural Join funktioniert nicht, da neben der abtnr auch noch das Attribut name in beiden Tabellen vorkommt. select * from angestellte
join abteilung using (abtnr);
(i2) Geben Sie alle Angestellten inklusive der Abteilungsdaten, zu der sie gehören,
aus. Angestellte, die keiner Abteilung zugeordnet sind sollen aber auch aufscheinen.
select * from angestellte left join abteilung using (abtnr);
Vertauscht man die Reihenfolge beim Joinen, muss statt dem left join ein right
join verwendet werden:
select * from abteilung right join angestellte using (abtnr);
2
(j) Geben Sie alle Projekte inklusive den Daten des Projektleiters aus. Projekte
ohne Leiter sollen aber auch im Ergebnis aufscheinen.
select * from projekt left join angestellte on (pleiter=angnr);
(k) Ermitteln Sie wie viele Projekte an den jeweiligen Standorten (pfiliale) angesiedelt sind.
select pfiliale, count(*) from projekt group by pfiliale;
(l) Geben Sie alle Angestellten aus, die in derselben Abteilung arbeiten, wie der
Angestellte mit der Nr 1435.
Mit Subquery. Zuerst wird die Abteilungsnummer der betreffenden Person ermittelt. Dann werden alle Angestellten gesucht mit derselben Abteilungsnummer.
select * from angestellte where abtnr in (select abtnr from
angestellte where angnr=1435)
NULL ist ungleich NULL. Deshalb kommt bei diesem Mitarbeiter nichts raus.
Die Abfrage ist aber korrekt. Anm: Statt abtnr in (...) kann auch abtnr=(...)
verwendet werden, da von der Subquery nur ein Wert zurück erwartet wird.
3