PROMPT Wintersemester 2001
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 laender
DROP TABLE laender;
CREATE TABLE laender (name CHAR(20), pke NUMBER(10,2));
INSERT INTO laender VALUES( 'Argentinien', 10);
INSERT INTO laender VALUES( 'Brasilien', 20);
INSERT INTO laender VALUES( 'USA', 110);
INSERT INTO laender VALUES( 'Deutschland', 101);
SELECT * FROM laender;


PROMPT Aufgabe 1 a)
SELECT name FROM laender
   WHERE pke = (SELECT MIN(pke) FROM laender)
      OR pke = (SELECT MAX(pke) FROM laender);

PROMPT Aufgabe 1 b)
SELECT AVG(pke) FROM laender;

PROMPT Aufgabe 1 c)
ALTER TABLE laender ADD (klasse NUMBER(3));

PROMPT Aufgabe 1 d)
PROMPT 10 sei das Minimum, 110 das Maximum:
UPDATE laender SET klasse = TRUNC((pke-10)*5/(110-10)-0.5)+1;
SELECT * FROM laender;

PROMPT Aufgabe 1 e)
SELECT klasse, COUNT(*) FROM laender GROUP BY klasse;


PROMPT Aufgabe 2:
PROMPT Anlegen der Tabellen
DROP TABLE wanderungen;
CREATE TABLE wanderungen (von_gemeinde CHAR(8),
                          nach_gemeinde CHAR(8),
                          personen NUMBER(6));
INSERT INTO wanderungen VALUES('08215047','08235033',10);
INSERT INTO wanderungen VALUES('08235033','08215047',2);
INSERT INTO wanderungen VALUES('08215047','08215046',5);
INSERT INTO wanderungen VALUES('08211000','08235033',7);
INSERT INTO wanderungen VALUES('08212000','08235032',20);
INSERT INTO wanderungen VALUES('08143016','08235031',10);
INSERT INTO wanderungen VALUES('08211000','08211000',3);
INSERT INTO wanderungen VALUES('08212000','08212000',12);
SELECT * FROM wanderungen;

PROMPT Aufgabe 2 a)
SELECT * FROM wanderungen
       WHERE von_gemeinde = nach_gemeinde;

PROMPT Aufgabe 2 b)
DROP VIEW zuzuege;
CREATE VIEW zuzuege ( gemeinde, personen) AS
   SELECT nach_gemeinde, SUM(personen) FROM wanderungen
       WHERE von_gemeinde <> nach_gemeinde 
       GROUP BY nach_gemeinde;
SELECT * FROM zuzuege;

PROMPT Aufgabe 2 c)
SELECT TRUNC(von_gemeinde/1000) "Kreis", SUM(personen) "Umzuege" 
   FROM wanderungen
   WHERE TRUNC(von_gemeinde/1000) = TRUNC(nach_gemeinde/1000)
   GROUP BY TRUNC(von_gemeinde/1000);

PROMPT Aufgabe 2 d)
DROP VIEW fortzuege;
CREATE VIEW fortzuege (gemeinde, personen) AS
   SELECT von_gemeinde, SUM(personen) FROM wanderungen
       WHERE von_gemeinde <> nach_gemeinde 
       GROUP BY von_gemeinde;
DROP VIEW wanderungssaldo;
CREATE VIEW wanderungssaldo (gemeinde, personen) AS
   SELECT a.gemeinde, a.personen - b.personen 
      FROM zuzuege a, fortzuege b
      WHERE a.gemeinde = b.gemeinde;
SELECT * FROM wanderungssaldo;

PROMPT Aufgabe 3:
PROMPT Anlegen der Tabellen
DROP TABLE segmente;
CREATE TABLE segmente (segmentID NUMBER, 
                       gemeindelinks CHAR(8), 
                       gemeinderechts CHAR(8));
INSERT INTO segmente VALUES (1,'08215047','08211000');
INSERT INTO segmente VALUES (2,'08215045','08211000');
INSERT INTO segmente VALUES (3,'08215047','08215045');
INSERT INTO segmente VALUES (3,'08215049','08215045');
SELECT * FROM segmente;

DROP TABLE zentrale_orte;
CREATE TABLE zentrale_orte (gemeinde CHAR(8), funktion CHAR(8));
INSERT INTO zentrale_orte VALUES ('08211000','OZ');
INSERT INTO zentrale_orte VALUES ('08215047',' ');
INSERT INTO zentrale_orte VALUES ('08215045',' ');
INSERT INTO zentrale_orte VALUES ('08215049',' ');
SELECT * FROM zentrale_orte;

PROMPT Aufgabe 3 a)
PROMPT Geben Sie eine Gemeinde ein:
ACCEPT gem 
SELECT gemeindelinks "Nachbarn" FROM segmente
   WHERE gemeinderechts = &gem
UNION
SELECT gemeinderechts "Nachbarn" FROM segmente
   WHERE gemeindelinks = &gem;
 

PROMPT Aufgabe 3 b)
DROP VIEW nachbarn;
CREATE VIEW nachbarn (gemeinde1, gemeinde2) AS
   SELECT gemeinderechts, gemeindelinks FROM segmente;
SELECT * FROM nachbarn;

PROMPT Aufgabe 3 c)
ALTER TABLE zentrale_orte ADD (gehoert_zum_oz CHAR(8));
SELECT * FROM zentrale_orte;

PROMPT Aufgabe 3 d)
UPDATE zentrale_orte z1 SET gehoert_zum_oz = 
   (SELECT z2.gemeinde FROM zentrale_orte z2, segmente s
      WHERE z2.funktion = 'OZ' AND
      z2.gemeinde = s.gemeindelinks AND 
      z1.gemeinde = s.gemeinderechts)
   WHERE EXISTS
   (SELECT z2.gemeinde FROM zentrale_orte z2, segmente s
      WHERE z2.funktion = 'OZ' AND
      z2.gemeinde = s.gemeindelinks AND 
      z1.gemeinde = s.gemeinderechts)
;
SELECT * FROM zentrale_orte;
UPDATE zentrale_orte z1 SET gehoert_zum_oz = 
   (SELECT z2.gemeinde FROM zentrale_orte z2, segmente s
      WHERE z2.funktion = 'OZ' AND
      z2.gemeinde = s.gemeinderechts AND 
      z1.gemeinde = s.gemeindelinks)
   WHERE EXISTS 
   (SELECT z2.gemeinde FROM zentrale_orte z2, segmente s
      WHERE z2.funktion = 'OZ' AND
      z2.gemeinde = s.gemeinderechts AND 
      z1.gemeinde = s.gemeindelinks);
SELECT * FROM zentrale_orte;
UPDATE zentrale_orte SET gehoert_zum_oz = gemeinde
   WHERE funktion = 'OZ';
SELECT * FROM zentrale_orte;

PROMPT Aufgabe 3 e)
UPDATE segmente s SET gemeindelinks =
   (SELECT z.gehoert_zum_oz FROM zentrale_orte z
      WHERE s.gemeindelinks = z.gemeinde AND
      z.gehoert_zum_oz <> ' ')
   WHERE EXISTS
   (SELECT z.gehoert_zum_oz FROM zentrale_orte z
      WHERE s.gemeindelinks = z.gemeinde AND
      z.gehoert_zum_oz <> ' ');
SELECT * FROM segmente;
UPDATE segmente s SET gemeinderechts =
   (SELECT z.gehoert_zum_oz FROM zentrale_orte z
      WHERE s.gemeinderechts = z.gemeinde AND 
      z.gehoert_zum_oz <> ' ')
   WHERE EXISTS
   (SELECT z.gehoert_zum_oz FROM zentrale_orte z
      WHERE s.gemeinderechts = z.gemeinde AND 
      z.gehoert_zum_oz <> ' ');
SELECT * FROM segmente;
DELETE FROM segmente
   WHERE gemeindelinks = gemeinderechts;
SELECT * FROM segmente;