PROMPT Wintersemester 2002
PROMPT Leistungsnachweis K 849 Pruefung P2
PROMPT 
PROMPT Etliche der folgenden Programmzeilen sind nicht Teil
PROMPT der Loesung, sondern machen die Zeilen ablauffaehig
PROMPT und dienen damit lediglich der Ueberpruefung
PROMPT der Korrektheit der Loesung.
PROMPT 

PROMPT Aufgabe 1:
PROMPT Anlegen der Tabelle gemeindegebietsreform
DROP TABLE gemeindegebietsreform;
CREATE TABLE gemeindegebietsreform (vor CHAR(20), nach CHAR(20));
INSERT INTO gemeindegebietsreform VALUES( 'v1', 'n2');
INSERT INTO gemeindegebietsreform VALUES( 'v2', 'n4');
INSERT INTO gemeindegebietsreform VALUES( 'v3', 'n3');
INSERT INTO gemeindegebietsreform VALUES( 'v4', 'n1');
INSERT INTO gemeindegebietsreform VALUES( 'v5', 'n4');
INSERT INTO gemeindegebietsreform VALUES( 'v6', 'n2');
INSERT INTO gemeindegebietsreform VALUES( 'v7', 'n1');
INSERT INTO gemeindegebietsreform VALUES( 'v8', 'n3');
INSERT INTO gemeindegebietsreform VALUES( 'aussen', 'aussen');
SELECT * FROM gemeindegebietsreform;

PROMPT Aufgabe 1 b)
PROMPT Anlegen der Tabelle grenzsegmentevorreform
DROP TABLE grenzsegmentevorreform;
CREATE TABLE grenzsegmentevorreform(sid CHAR(20),
                                    gemeindelinks CHAR(20),
                                    gemeinderechts CHAR(20));
INSERT INTO grenzsegmentevorreform VALUES( 's1', 'v3', 'v4');
INSERT INTO grenzsegmentevorreform VALUES( 's2', 'v4', 'aussen');
INSERT INTO grenzsegmentevorreform VALUES( 's3', 'v2', 'v7');
INSERT INTO grenzsegmentevorreform VALUES( 's4', 'v3', 'v6');
INSERT INTO grenzsegmentevorreform VALUES( 's5', 'v7', 'v8');
INSERT INTO grenzsegmentevorreform VALUES( 's6', 'v8', 'aussen');
INSERT INTO grenzsegmentevorreform VALUES( 's7', 'v7', 'aussen');
INSERT INTO grenzsegmentevorreform VALUES( 's8', 'v1', 'v4');
INSERT INTO grenzsegmentevorreform VALUES( 's9', 'v3', 'aussen');
INSERT INTO grenzsegmentevorreform VALUES( 's10', 'v4', 'v7');
INSERT INTO grenzsegmentevorreform VALUES( 's11', 'v5', 'v8');
INSERT INTO grenzsegmentevorreform VALUES( 's12', 'v1', 'v6');
INSERT INTO grenzsegmentevorreform VALUES( 's13', 'v2', 'v5');
INSERT INTO grenzsegmentevorreform VALUES( 's14', 'v3', 'v8');
INSERT INTO grenzsegmentevorreform VALUES( 's15', 'v1', 'v2');
INSERT INTO grenzsegmentevorreform VALUES( 's16', 'v5', 'v6');

PROMPT Aufgabe 1 c)
DROP TABLE grenzsegmentenachreform;
CREATE TABLE grenzsegmentenachreform(sid,
                                    gemeindelinks,
                                    gemeinderechts)
AS
SELECT s.sid, a.nach, b.nach FROM grenzsegmentevorreform s,
                                gemeindegebietsreform a,
                                gemeindegebietsreform b
WHERE s.gemeindelinks=a.vor and s.gemeinderechts=b.vor;
SELECT * FROM grenzsegmentenachreform ORDER BY sid;

PROMPT Aufgabe 1 d)
DELETE FROM grenzsegmentenachreform
   WHERE gemeindelinks = gemeinderechts;
SELECT * FROM grenzsegmentenachreform;


PROMPT Aufgabe 2 a)
PROMPT Anlegen der Tabelle l_h
DROP TABLE l_h;
CREATE TABLE l_h(linie NUMBER(3), hst NUMBER(3));
INSERT INTO l_h VALUES(1,11);
INSERT INTO l_h VALUES(1,12);
INSERT INTO l_h VALUES(1,13);
INSERT INTO l_h VALUES(1,14);
INSERT INTO l_h VALUES(1,15);
INSERT INTO l_h VALUES(1,16);
INSERT INTO l_h VALUES(1,17);
INSERT INTO l_h VALUES(2,21);
INSERT INTO l_h VALUES(2,22);
INSERT INTO l_h VALUES(2,15);
INSERT INTO l_h VALUES(2,23);
INSERT INTO l_h VALUES(2,24);
INSERT INTO l_h VALUES(2,25);
INSERT INTO l_h VALUES(2,26);
INSERT INTO l_h VALUES(3,31);
INSERT INTO l_h VALUES(3,32);
INSERT INTO l_h VALUES(3,13);
INSERT INTO l_h VALUES(3,33);
INSERT INTO l_h VALUES(3,34);
INSERT INTO l_h VALUES(3,35);
INSERT INTO l_h VALUES(3,36);
INSERT INTO l_h VALUES(4,41);
INSERT INTO l_h VALUES(4,42);
INSERT INTO l_h VALUES(4,24);
INSERT INTO l_h VALUES(4,43);
INSERT INTO l_h VALUES(4,44);
INSERT INTO l_h VALUES(5,51);
INSERT INTO l_h VALUES(5,52);
INSERT INTO l_h VALUES(5,34);
INSERT INTO l_h VALUES(5,53);
INSERT INTO l_h VALUES(5,54);
SELECT * FROM l_h;

PROMPT Aufgabe 2 b)
PROMPT Geben Sie eine Haltestelle ein:
ACCEPT myh

PROMPT Aufgabe 2 c)
SELECT DISTINCT hst FROM l_h
WHERE linie IN 
(SELECT linie FROM l_h WHERE hst=&myh);

PROMPT Aufgabe 2 d)
SELECT DISTINCT linie FROM l_h
WHERE hst IN 
(SELECT hst FROM l_h
WHERE linie IN 
(SELECT linie FROM l_h WHERE hst=&myh));

PROMPT Aufgabe 2 d)
SELECT linie FROM l_h
MINUS
SELECT DISTINCT linie FROM l_h
WHERE hst IN 
(SELECT hst FROM l_h
WHERE linie IN 
(SELECT linie FROM l_h WHERE hst=&myh));

PROMPT Aufgabe 3:
PROMPT Anlegen der Tabellen
DROP TABLE statistik;
CREATE TABLE statistik(gemeinde CHAR(8), 
                       kreis CHAR(8),
                       beschaeftigte NUMBER(8),
                       arbeitslosenquote NUMBER(5,2));
INSERT INTO statistik VALUES( 'v1', 'n2', 2500, 4.32);
INSERT INTO statistik VALUES( 'v2', 'n4', 7500, 9.11);
INSERT INTO statistik VALUES( 'v3', 'n3', 2000, 10.43);
INSERT INTO statistik VALUES( 'v4', 'n1', 8000, 10.54);
INSERT INTO statistik VALUES( 'v5', 'n4', 8500, 11.26);
INSERT INTO statistik VALUES( 'v6', 'n2', 1500, 3.87);
INSERT INTO statistik VALUES( 'v7', 'n1', 2000, 4.21);
INSERT INTO statistik VALUES( 'v8', 'n3', 8000, 4.57);
SELECT * FROM statistik;

PROMPT Aufgabe 3 a)
SELECT gemeinde, arbeitslosenquote FROM statistik
WHERE arbeitslosenquote=(SELECT MAX(arbeitslosenquote) FROM statistik)
OR arbeitslosenquote=(SELECT MIN(arbeitslosenquote) FROM statistik); 

PROMPT Aufgabe 3 b)
SELECT kreis, SUM(beschaeftigte*arbeitslosenquote/100) "Arbeitslose" FROM statistik
GROUP BY kreis;

PROMPT Aufgabe 3 c)
SELECT kreis, SUM(beschaeftigte)/SUM(beschaeftigte*arbeitslosenquote/100) "Arbeitslosenquote" FROM statistik
GROUP BY kreis;