PROMPT Wintersemester 1997
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 umsteigebahnhoefe;
CREATE TABLE umsteigebahnhoefe (bhf_nr NUMBER(3),
                            linie_nr NUMBER(3));
INSERT INTO umsteigebahnhoefe VALUES(1,7);
INSERT INTO umsteigebahnhoefe VALUES(3,7);
INSERT INTO umsteigebahnhoefe VALUES(8,7);
INSERT INTO umsteigebahnhoefe VALUES(2,7);
INSERT INTO umsteigebahnhoefe VALUES(3,2);
INSERT INTO umsteigebahnhoefe VALUES(2,2);
INSERT INTO umsteigebahnhoefe VALUES(4,3);
INSERT INTO umsteigebahnhoefe VALUES(3,3);
INSERT INTO umsteigebahnhoefe VALUES(5,3);
INSERT INTO umsteigebahnhoefe VALUES(1,4);
INSERT INTO umsteigebahnhoefe VALUES(6,4);
INSERT INTO umsteigebahnhoefe VALUES(13,3);
SELECT * FROM umsteigebahnhoefe;

PROMPT Aufgabe 1 a)
SELECT bhf_nr, linie_nr FROM umsteigebahnhoefe
       WHERE linie_nr = 7 and bhf_nr<>3;

PROMPT Aufgabe 1 b)
SELECT bhf_nr, linie_nr FROM umsteigebahnhoefe 
       WHERE linie_nr IN (
       SELECT linie_nr FROM umsteigebahnhoefe
              WHERE bhf_nr = 3)
       AND bhf_nr <> 3;


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('08215047','08215046',5);
INSERT INTO wanderungen VALUES('08211000','08235033',7);
INSERT INTO wanderungen VALUES('08212000','08235032',20);
INSERT INTO wanderungen VALUES('08143016','08235031',10);
SELECT * FROM wanderungen;

PROMPT Aufgabe 2 a)
SELECT SUM(personen) FROM wanderungen
       WHERE nach_gemeinde = '08235033';
SELECT SUM(personen) FROM wanderungen
       WHERE von_gemeinde = '08215047';

PROMPT Aufgabe 2 b)
SELECT SUM(personen) FROM wanderungen
       WHERE TRUNC(nach_gemeinde/1000) = TRUNC('08235033'/1000);


PROMPT Aufgabe 2 c)
PROMPT Einfuegen inneroertlicher Umzuege
INSERT INTO wanderungen VALUES('08215047','08215047',3);
INSERT INTO wanderungen VALUES('08235033','08235033',8);
SELECT * FROM wanderungen;

SELECT SUM(personen) FROM wanderungen
       WHERE nach_gemeinde = '08235033' AND von_gemeinde <> nach_gemeinde;
SELECT SUM(personen) FROM wanderungen
       WHERE von_gemeinde = '08215047' AND von_gemeinde <> nach_gemeinde;
 
SELECT SUM(personen) FROM wanderungen
       WHERE TRUNC(nach_gemeinde/1000) = TRUNC('08235033'/1000)
       AND von_gemeinde <> nach_gemeinde;



PROMPT Aufgabe 3
PROMPT Anlegen der Tabellen
DROP TABLE karten;
CREATE TABLE karten (lfd_nr NUMBER(6),
                     titel CHAR(20),
                     weitere_kartenangaben CHAR(6),
                     autor1 CHAR(10),
                     autor2 CHAR(10),
                     autor3 CHAR(10));
INSERT INTO karten VALUES(1,'Alte Karte','farbig','Kolbe',NULL,NULL);
INSERT INTO karten VALUES(1007,'Hamburg','s/w','Hansmann',NULL,'Frey');
INSERT INTO karten VALUES(1034,'Berlin','bunt','Frey','Hiller','Hansmann');
SELECT * FROM karten;

DROP TABLE autorenadressen;
CREATE TABLE autorenadressen (lfd_nr NUMBER(6),
                              name CHAR(10),
                              adresse CHAR(15));
INSERT INTO autorenadressen VALUES(1,'Kolbe','Dorfstr. 10');
INSERT INTO autorenadressen VALUES(2,'Hansmann','Poststr. 3');
INSERT INTO autorenadressen VALUES(3,'Frey','Hauptstr. 110');
INSERT INTO autorenadressen VALUES(4,'Hiller','Marktplatz 4');
INSERT INTO autorenadressen VALUES(5,'Maier','Heldentor 1');
SELECT * FROM autorenadressen;

PROMPT Aufgabe 3 a)
SELECT autor1, adresse FROM karten, autorenadressen
       WHERE name=autor1 AND karten.lfd_nr BETWEEN 1007 AND 1034
  UNION
SELECT autor2, adresse FROM karten, autorenadressen
       WHERE name=autor2 AND karten.lfd_nr BETWEEN 1007 AND 1034
  UNION
SELECT autor3, adresse FROM karten, autorenadressen
       WHERE name=autor3 AND karten.lfd_nr BETWEEN 1007 AND 1034;

PROMPT Aufgabe 3 b)
DROP TABLE karten_neu;
CREATE TABLE karten_neu (lfd_nr, titel, weitere_kartenangaben) AS
       SELECT lfd_nr, titel, weitere_kartenangaben FROM karten;
SELECT * FROM karten_neu;

DROP TABLE karten_autoren;
CREATE TABLE karten_autoren (lfd_nr_karte, lfd_nr_autor) AS
       SELECT karten.lfd_nr, autorenadressen.lfd_nr 
              FROM karten, autorenadressen
              WHERE autor1=name OR autor2=name OR autor3=name; 
SELECT * FROM karten_autoren;