PROMPT Sommersemester 2003
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
PROMPT Anlegen der Tabellen
DROP TABLE werte;
CREATE TABLE werte (lfd NUMBER(3), sp1 NUMBER(3), sp2 NUMBER(3));
INSERT INTO werte VALUES (1, 1, 4);
INSERT INTO werte VALUES (2, 1, 3);
INSERT INTO werte VALUES (3, 2, 2);
INSERT INTO werte VALUES (4, 2, 1);
INSERT INTO werte VALUES (5, 2, 4);
INSERT INTO werte VALUES (6, 2, 3);
INSERT INTO werte VALUES (7, 1, 2);
INSERT INTO werte VALUES (8, 1, 1);
SELECT * FROM werte;

PROMPT Aufgabe 2 a)
SELECT sp1, SUM(sp2), AVG(sp2) FROM werte GROUP BY sp1;

PROMPT Aufgabe 2 b)
SELECT * FROM werte ORDER BY sp1, sp2;
SELECT lfd, sp2, sp1 FROM werte ORDER BY sp2, sp1 DESC;

PROMPT Aufgabe 2 c)
SELECT a.lfd, a.sp1, a.sp2, b.lfd, b.sp1, b.sp2 FROM werte a, werte b
   WHERE a.sp1=b.sp2;

PROMPT Aufgabe 2 d)
PROMPT Es ist ein Auto-Equi-Join, also ein Join einer Tabelle mit sich selbst
PROMPT und einer WHERE-Bedingung mit Gleichheitszeichen. 

PROMPT Aufgabe 3:
PROMPT Anlegen der Tabellen
DROP TABLE kreis;
CREATE TABLE kreis (nummer CHAR(8), name CHAR(20), zentrum CHAR(20), rechts number, hoch number);
INSERT INTO kreis VALUES ('08215','Karlsruhe, Landkreis','Karlsruhe', 3452950, 5432580);
INSERT INTO kreis VALUES ('08216','Rastatt, Landkreis','Rastatt', 3437860, 5413850);
SELECT * FROM kreis;

DROP TABLE gemeinden;
CREATE TABLE gemeinden (name CHAR(20), kreis CHAR(8), einwohner number, rechts number, hoch number);
INSERT INTO gemeinden VALUES ('Bietigheim','08216',5919, 3444680, 5420175);
INSERT INTO gemeinden VALUES ('Durmersheim','08216',12049, 3446735, 5421951);
INSERT INTO gemeinden VALUES ('Ettlingen','08215', 38972, 3456876, 5423197);
INSERT INTO gemeinden VALUES ('Karlsruhe','08215', 281334, 3455647, 5430535);
INSERT INTO gemeinden VALUES ('Kuppenheim','08216',7621, 3445690, 5410499);
INSERT INTO gemeinden VALUES ('Malsch','08215',14107, 3451572, 5416422);
INSERT INTO gemeinden VALUES ('Marxzell','08215', 5492, 3459627, 5412887);
INSERT INTO gemeinden VALUES ('Rastatt','08216',47688, 3442405, 5414589);
INSERT INTO gemeinden VALUES ('Rheinstetten','08215',20406, 3448564, 5425035);
SELECT * FROM gemeinden;

PROMPT Aufgabe 3 a)
SELECT g.name, k.zentrum FROM gemeinden g,kreis k
   WHERE g.kreis=k.nummer;

PROMPT Aufgabe 3 b)
SELECT kreis, SUM(einwohner) "Einwohner" FROM gemeinden GROUP BY kreis;

PROMPT Aufgabe 3 c)
DROP VIEW gem15a;
CREATE VIEW gem15a AS
   SELECT g.name, k.zentrum "Nächstes Zentrum" FROM gemeinden g, kreis k
      WHERE SQRT((g.rechts - k.rechts) 
                * (g.rechts - k.rechts) 
                + (g.hoch - k.hoch) 
                * (g.hoch - k.hoch)) < 15000; 
SELECT * FROM gem15a;

PROMPT Aufgabe 3 d)
DROP VIEW gem15b;
CREATE VIEW gem15b AS
   SELECT g.name, k.zentrum "Zughöriges Zentrum" FROM gemeinden g, kreis k
      WHERE g.kreis=k.nummer
         AND SQRT((g.rechts - k.rechts) 
                * (g.rechts - k.rechts) 
                + (g.hoch - k.hoch) 
                * (g.hoch - k.hoch)) < 15000; 
SELECT * FROM gem15b;

PROMPT Aufgabe 3 e)
DROP VIEW gem15c;
CREATE VIEW gem15c AS
   SELECT g.name, k.zentrum FROM gemeinden g, kreis k
      WHERE SQRT((g.rechts - k.rechts) 
                * (g.rechts - k.rechts) 
                + (g.hoch - k.hoch) 
                * (g.hoch - k.hoch)) < 15000
INTERSECT
   SELECT g.name, k.zentrum FROM gemeinden g, kreis k
      WHERE g.kreis=k.nummer
         AND SQRT((g.rechts - k.rechts) 
                * (g.rechts - k.rechts) 
                + (g.hoch - k.hoch) 
                * (g.hoch - k.hoch)) < 15000; 
SELECT * FROM gem15c;