PROMPT Wintersemester 2000
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 Tabellen
DROP TABLE mitarbeiter;
CREATE TABLE mitarbeiter (name CHAR(10), st_m number, st_v number);
INSERT INTO mitarbeiter VALUES ('Chef',0,0);
INSERT INTO mitarbeiter VALUES ('Abt1',1,0);
INSERT INTO mitarbeiter VALUES ('Abt2',2,0);
INSERT INTO mitarbeiter VALUES ('Mit',3,1);
SELECT * FROM mitarbeiter;

SELECT a.name "Mitarb", b.name "Vorges" FROM mitarbeiter a, mitarbeiter b
WHERE a.st_v=b.st_m;


PROMPT Aufgabe 2:
PROMPT Anlegen der Tabellen
DROP TABLE c;
CREATE TABLE c (ort CHAR(10), rw number, hw number);
INSERT INTO c VALUES ('Karlsruhe',3456, 5431);
INSERT INTO c VALUES ('Ettlingen',3451, 5424);
INSERT INTO c VALUES ('Rastatt',3462, 5471);
INSERT INTO c VALUES ('Mannheim',3419, 5444);
SELECT * FROM c;


PROMPT Die Datei tricky.txt
SELECT ort FROM c 
   WHERE ort < '&1' and (ABS(rw - &2) + ABS(hw - &3)) < 30;

PROMPT Die geaenderte Datei tricky_mod.txt
SELECT ort FROM c 
   WHERE ort < '&&1' and 
   (ABS(rw - (SELECT rw FROM c WHERE ort='&&1')) 
  + ABS(hw - (SELECT hw FROM c WHERE ort='&&1'))) < 30;


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;