Informationssysteme IS-K WS 1999/2000 Praktische Ubung 3
Werbung
Prof. H.-J. Schek
Eidgenossische
Technische Hochschule
Z
urich
Ecole polytechnique federale de Zurich
Politecnico federale di Zurigo
Swiss Federal Institute of Technology Zurich
T. Grabs, A. Popovici, U. Rohm, C. Schuler, R. Weber
Informationssysteme IS-K WS 1999/2000
Praktische U bung 3
Beispiellosung
Eine Personendatenbank
Das Skript der Beispiellosung nden Sie unter
http://www-dbs.ethz.ch/isk/WS 99/uebungen/pu3 lsg.sql.
Aufgabe 1: Einfache Anfragen und A nderungen
a) Welche Fremdsprachen spricht 'Alex Zuelle' gut (Skill>=7)?
SELECT s.Name,k.Skill
FROM THE(SELECT Languages FROM Person WHERE Name='Zuelle') k, LangList s
WHERE k.LangID=s.ID AND s.Typ='FOREIGN' AND k.Skill>=7;
b) Welche Computerspezialisten sprechen Ungarisch?
SELECT
FROM
WHERE
AND
p.Name,p.FirstName
Person p
p.Job='Computerspezialist'
EXISTS(SELECT * FROM THE(SELECT Languages FROM Person p2 WHERE p2.id=p.id) k,
LangList s
WHERE k.LangID=s.id AND s.Name='Ungarisch');
c) Welcher Sportler beherrscht auch eine Programmiersprache?
SELECT p.Name,p.FirstName FROM Person p
WHERE p.Job='Sportler'
AND EXISTS(SELECT *
FROM THE(SELECT Languages FROM Person p2 WHERE p2.id=p.id) k,
LangList s
WHERE s.ID=k.LangID AND s.Typ='PROG');
d) Welche deutsch sprechende Person verdient am meisten und wieviel?
Bill Gates spricht naturlich nicht Deutsch. Entfernen Sie diesen Eintrag. Welche deutsch
sprechende Person verdient jetzt am meisten und wieviel?
SELECT p.Name,p.FirstName,p.Salary
FROM Person p
WHERE EXISTS(SELECT * FROM THE(SELECT Languages FROM Person p2 WHERE p2.id=p.id) k,
LangList s
WHERE k.LangID=s.id AND s.Name='Deutsch')
AND p.Salary>=ALL
(SELECT p2.Salary FROM Person p2
WHERE EXISTS(SELECT *
FROM THE(SELECT Languages FROM Person p3 WHERE p3.id=p2.id) k,
LangList s
WHERE k.LangID=s.id AND s.Name='Deutsch'));
DELETE THE(SELECT Languages FROM Person p WHERE p.Name='Gates')
WHERE LangID=(SELECT id FROM LangList s WHERE s.Name='Deutsch');
e) Fugen Sie zwei Studenten (Job='Student') ihrer Wahl ein. Der erste Student soll unter
anderem die Programmiersprache Java kennen (Skill=6). Der andere Student kennt
Java nicht. Alle anderen Kenntnisse konnen beliebig gesetzt werden.
INSERT INTO Person VALUES (30,'Peter','Ziegenhirt',0,'Student',KnowledgeTab(
KnowledgeRec(1,7),KnowledgeRec(2,6),KnowledgeRec(4,2),KnowledgeRec(51,7),
KnowledgeRec(8,9),KnowledgeRec(52,4),KnowledgeRec(56,6)));
INSERT INTO Person VALUES (1030,'Heidi','von der Alp',0,'Student',KnowledgeTab(
KnowledgeRec(1,8),KnowledgeRec(2,8),KnowledgeRec(3,7),KnowledgeRec(4,7),
KnowledgeRec(8,9),KnowledgeRec(54,7),KnowledgeRec(55,8),KnowledgeRec(60,3)));
Aufgabe 2: Methoden
a) Schreiben Sie eine Methode Tax(rate), welche die Steuern einer Person berechnet.
Dazu muss in der Tabelle TaxList(Salary,TaxRate) der Eintrag gesucht werden, dessen Salary-Wert gerade grosser ist als das Einkommen der Person. Der Wert TaxRate
gibt dann den Basissteuersatz an. Die Anweisung SELECT * FROM TaxList; zeigt ihnen die aktuellen Steuersatze. Verdient eine Person z.B. 25000 Franken, dann ist der
Eintrag (50000,6) relevant; bei einem Einkommen von 102000, der Eintrag (200000,10).
Der ausgelesene TaxRate-Wert ist der Basissteuersatz in Prozent und muss mit dem
rate-Parameter der Methode Tax multipliziert werden (rate ist wiederum in Prozenten
gemessen). Damit sieht die Berechnung also wie folgt aus:
Bestimme (Salary,TaxRate)-Tupel aus TaxList, so dass Salary gerade grosser
ist als das Einkommen (=Self.Salary)
Die Steuerabgaben betragen dann: Self.Salary*TaxRate/100*rate/100
Hinweis: Schreiben Sie erst eine SQL-Select-Anweisung, welche Ihnen den Steursatz
fur ein fest gegebenes Einkommen aus der Tabelle TaxList ausliest.
MEMBER FUNCTION Tax(rate REAL) RETURN REAL IS /* Steursatz in % (110 => 110%) */
baseTaxRate REAL;
BEGIN
SELECT s.TaxRate
INTO baseTaxRate
FROM TaxList s
WHERE s.Salary>=Self.Salary
AND s.Salary<=ALL(SELECT t.Salary FROM TaxList t WHERE t.Salary>=Self.Salary);
RETURN ROUND(Self.Salary*baseTaxRate/100.0*rate/100.0,1);
END Tax;
b) Die Methoden MaxLanguage(Type) und MaxLanguageSkill(Type) geben die Sprache
und die Kenntnisse der best beherrschten Sprache eines bestimmten Typs zuruck (Type
ist entweder 'PROG' oder 'FOREIGN'). Die Methode MaxLanguage ist bereits vorhanden.
Sie mussen nur noch MaxLanguageSkill programmieren.
MEMBER FUNCTION MaxLanguageSkill(Type VARCHAR2) RETURN INTEGER IS
sk INTEGER;
BEGIN
SELECT MAX(Skill) INTO sk
FROM THE(SELECT Languages FROM Person p WHERE p.ID=Self.id) l,LangList s
WHERE l.LangID=s.ID AND s.Typ=Type;
RETURN sk;
EXCEPTION
WHEN OTHERS THEN RETURN NULL;
END MaxLanguageSkill;
c) Welche Sportler zahlen mehr als 1000 Franken Sozialbeitrag?
SELECT RPAD(p.Name || ' ' || p.FirstName,32) Name,p.Salary,p.SocialContrib() Social
FROM Person p
WHERE p.SocialContrib()>1000 AND p.Job='Sportler';
d) Ermitteln Sie die Anzahl, das durchschnittliche Einkommen, die durchschnittlichen
Steuern, die durchschnittliche Anzahl Programmiersprachen und die durschnittliche
Anzahl gesprochener Fremdsprachen der Personen desselben Berufs.
Hinweis: Benutzen Sie die Mehtode NumLanguage, welche die Anzahl Sprachen eines
Typs fur eine Person ermittelt.
SELECT RPAD(p.Job,20) Beruf,COUNT(*) Anzahl,ROUND(AVG(p.Salary),1) Einkommen,
ROUND(AVG(p.Tax(110)),1) Steuern,ROUND(AVG(p.NumLanguage('PROG')),1) nProg,
ROUND(AVG(p.NumLanguage('FOREIGN')),1) nFremd
FROM Person p
GROUP BY p.Job;
e) In welcher Berufsgruppe wird durchschnittlich am meisten/wenigsten Steuern bezahlt?
SELECT RPAD(p.Job,20) Beruf,ROUND(AVG(p.Salary),1) Einkommen,
ROUND(AVG(p.Tax(110)),1) Steuern
FROM Person p
GROUP BY p.Job
HAVING AVG(p.Tax(110))>=ALL
(SELECT AVG(p.Tax(110)) FROM Person p GROUP BY p.Job);
SELECT RPAD(p.Job,20) Beruf,ROUND(AVG(p.Salary),1) Einkommen,
ROUND(AVG(p.Tax(110)),1) Steuern
FROM Person p
GROUP BY p.Job
HAVING AVG(p.Tax(110))<=ALL
(SELECT AVG(p.Tax(110)) FROM Person p GROUP BY p.Job);
