Informatik für ¨Okonomen II

1
Informatik für Ökonomen II - Übung 1
Einleitung
Das Ziel dieser Übung ist es, Ihnen das Erstellen von Anfragen in SQL näher zu bringen
und so Ihr Verständnis dafür zu vertiefen. Die Aufgaben können sehr gut auf Papier gelöst
werden, ein Computer ist also nicht notwendig. Bitte verwenden Sie keinen Bleistift.
Am Ende dieses Übungsblattes ist Schema und Ausprägung der in dieser Übung verwendeten Datenbank abgebildet. Ein Stern (*) neben einem Attribut bezeichnet eine
Fremdschlüsselbeziehung. Die Primärschlüssel-attribute einer Relation sind unterstrichen.
Aufgabe 1 (6 Punkte)
Schreiben Sie Abfragen, welche die folgenden Daten ermitteln. Geben Sie auch die Ergebnisse, die Sie erhalten an.
1. Den Nachnamen aller Angestellten, die ein Gehalt von 6000 haben.
2. Den Vor- und Nachnamen des Abteilungsleiters der Abteilung “Kundenservice”.
3. Den Vor- und Nachnamen aller weiblichen Angestellten, die mindestens 6000 verdienen.
Hinweise
Erwartet werden Antworten wie folgt:
ˆ SQL code:
SELECT * FROM K a e f i g
WHERE KNr = 1 ;
ˆ Ergebnisse:
KNr
Name
1
Tropical
Groesse
1840
Bauarbeiten
0
Bei UPDATE-Statements sind alle geänderten Tupel mit allen Werten (geändert
und unverändert) anzugeben.
Aufgabe 2 (36 Punkte)
Nennen und beschreiben Sie die Ergebnisse der folgenden Abfragen.
1.
SELECT a . VName, a . NName, t . Bezeichnung
FROM Ang a , E i n s a t z o r t e , T i e r e t
WHERE a .AHV = e .AHV AND e .KFG = t .KFG
ORDER BY t . Bezeichnung
LIMIT 0 , 5 ;
2.
SELECT e .AHV, SUM( k . G r o e s s e )
FROM E i n s a t z o r t e , K a e f i g k
WHERE e .KFG = k . KNr
GROUP BY e .AHV;
2
3.
SELECT AVG( a . Gehalt ) , k . Name
FROM Ang a , E i n s a t z o r t e , K a e f i g k
WHERE
e .KFG = k . KNr AND
k . Name= ’ T r o p i c a l ’ AND
e .AHV = a .AHV
GROUP BY k . Name ;
4.
UPDATE K a e f i g
SET G r o e s s e = G r o e s s e * 1 . 2
WHERE KNr IN
(SELECT KFG FROM T i e r e WHERE T i e r a l t e r >= 4 ) ;
Hinweise
Erwartet werden Antworten wie folgt:
ˆ Geben Sie eine KURZE Beschreibung der Anfrage in Prosa an, welche ALLE
relevanten Details der Queries nennt.
ˆ Geben Sie in Tabellenform (s. Hinweis Aufgabe 1) die Ergebnisse der Anfrage an.
Aufgabe 3 (38 Punkte)
Schreiben Sie Abfragen in SQL, welche die folgenden Fragen beantworten und geben Sie
die Antworten an (die aktuelle Ausprägung der Datenbank ist hierbei nicht massgeblich).
1. Geben Sie für alle Angestellten welche in keinem Käfig arbeiten die AHV-Nummer
und den Namen ihrer Abteilung an.
2. Geben Sie Vornamen und Nachnamen der jüngsten Angestellten an?
3. Geben Sie Vornamen und Nachnamen der Personen aus, welche in einem Käfig
arbeiten, der zwischen 15% und 20% grösser als die durchschnittliche Käfiggrösse
ist.
4. Das Gehalt aller Angestellten wird um 5% verringert, sofern sie in der Abteilung
’Management’ arbeiten.
5. Geben Sie das Gehalt und Vor- und Nachnamen aller Angestellten an, die in allen
Käfigen arbeiten.
Aufgabe 4 (20 Punkte)
Die folgenden Ereignisse bewirken Änderungen in der Datenbank. Schreiben Sie die Befehle, um die Datenbank anzupassen. Werden neue Tupel eingefügt, so soll bei numerischen
Primärschlüsseln die nächste noch nicht verwendete Zahl verwendet werden.
1. Eine neue Mitarbeiterin für die Abteilung “Kundenservice” wird hinzugefügt: Regina
Gehlen, AHV-Nr.:42, am 17. Januar 1964 geboren, arbeitet für 4200 unter Mirella
Freni.
2. Eine neue Abteilung “Marketing” wird ins Leben gerufen. Frau Emmy Noether wird
zur Abteilungsleiterin promoviert. Ihr Gehalt und ihre Vorgesetzte bleiben unverändert. Vergessen Sie nicht, die Tabbelle ’Ang’ gegebenenfalls zu aktualisieren!
3
3. Es können wegen klimatischer Gegebenheiten keine “Pinguine” mehr gehalten werden.
4. Die Riesenschildkröte “Harriet” wird im Zoo am 2009-07-24 aufgenommen. “Harriet”
ist 175 Jahre alt – sie wird im Käfig ’Tropical’ gehalten.
4
Beispieldatenbank
Ang
AHV
1
2
3
4
5
6
7
8
9
10
11
Kaefig
KNr
1
2
3
4
5
6
Abteilung
Tiere
NName
Huesli
Tell
Becker
Frisch
Maradona
Wagner
Verdi
Freni
Schlau
Braun
Noether
TID
1
2
3
4
5
6
VName
Kurt
Beate
Ruedi
Boris
Sonja
Guiseppe
Amando
Mirella
Adam
Tanja
Emmy
Name
Tropical
Arktisch
Kontinental
Safari
Wilder Westen
Aquarium
AID
1
2
3
4
5
6
Groesse
1840
680
1780
2460
2245
225
Name
Medizin
Reinigung
Fütterung
Kasse
Kundenservice
Management
Bezeichnung
Pinguin
Elefant
Büffel
Katzenhai
Papagei
Bär
Abt*
2
2
3
3
4
1
5
6
1
1
6
GebDatum
1973-12-30
1970-01-14
1979-12-24
1981-06-03
1974-11-11
1979-03-04
1972-08-19
1968-06-22
1955-05-15
1979-02-14
1882-03-23
Vorges*
2
8
4
8
8
8
8
8
6
6
8
Bauarbeiten
0
0
0
0
1
0
AbtLeiter*
6
2
4
5
7
8
KFG*
2
4
5
6
1
3
Ankunft
2006-08-09
2004-11-13
2005-07-02
2007-02-14
2007-04-03
2003-01-01
Tieralter
2
6
4
2
1
5
Gehalt
2300
3500
2500
6500
6000
6000
6000
8000
3000
4000
8000
Gesch
m
w
m
m
w
m
m
w
m
w
w
5
Einsatzort
AHV*
1
1
1
2
2
2
2
2
2
3
3
3
4
4
4
6
6
6
6
6
6
9
10
10
10
10
10
10
KFG*
1
2
3
1
2
3
4
5
6
1
2
3
4
5
6
1
2
3
4
5
6
6
1
2
3
4
5
6