PROMPT Sommersemester 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 tablea;
CREATE TABLE tablea 
             (columna1 CHAR(2), columna2 CHAR(2), columna3 CHAR(2));
INSERT INTO tablea VALUES('a1','a2','a3');
SELECT * FROM tablea;

DROP TABLE tableb;
CREATE TABLE tableb 
             (columnb1 CHAR(2), columnb2 CHAR(2), columnb3 CHAR(2));
INSERT INTO tableb VALUES('b1','b2','b3');
SELECT * FROM tableb;

DROP TABLE tablec;
CREATE TABLE tablec 
             (columnc1 CHAR(2), columnc2 CHAR(2), columnc3 CHAR(2));
INSERT INTO tablec VALUES('c1','c2','c3');
SELECT * FROM tablec;

PROMPT Die Startdatei startdatei.sql
SELECT &2 FROM &1;

PROMPT Die Dialog-Datei dialog.sql
PROMPT Welche Tabelle wollen Sie ansehen?
ACCEPT table
PROMPT Die Tabelle &table hat diese Spalten:
DESCRIBE &table
PROMPT Welche Spalte wollen Sie von Tabelle &table ansehen?
ACCEPT col
START z:\DB\20000721P2\Aufgabe1\startdatei &table &col

UNDEFINE 1
UNDEFINE 2
UNDEFINE table
UNDEFINE col


PROMPT Aufgabe 2:
PROMPT Anlegen der Tabellen
DROP TABLE gemeinden;
CREATE TABLE gemeinden 
             (gkz NUMBER, al NUMBER);
INSERT INTO gemeinden VALUES( 1, 1376);
INSERT INTO gemeinden VALUES( 2, 2324);
INSERT INTO gemeinden VALUES( 3,  987);
INSERT INTO gemeinden VALUES( 4,  423);
INSERT INTO gemeinden VALUES( 5, 1569);
INSERT INTO gemeinden VALUES( 6,  964);
INSERT INTO gemeinden VALUES( 7, 1563);
INSERT INTO gemeinden VALUES( 8,  356);
INSERT INTO gemeinden VALUES( 9, 2370);
INSERT INTO gemeinden VALUES(10,  389);
INSERT INTO gemeinden VALUES(11, 2553);
INSERT INTO gemeinden VALUES(12,  583);
INSERT INTO gemeinden VALUES(13,  276);
INSERT INTO gemeinden VALUES(14,  385);
INSERT INTO gemeinden VALUES(15, 1749);
INSERT INTO gemeinden VALUES(16,  387);
INSERT INTO gemeinden VALUES(17, 1265);
INSERT INTO gemeinden VALUES(18, 2398);
INSERT INTO gemeinden VALUES(19,  344);
INSERT INTO gemeinden VALUES(20,  987);
SELECT * FROM gemeinden;

PROMPT Aufgabe 2 a)
ALTER TABLE gemeinden ADD(aa NUMBER);

PROMPT Aufgabe 2 b)
UPDATE gemeinden SET aa=1 WHERE gkz IN (1,3,7,8,9,11,13);
UPDATE gemeinden SET aa=2 WHERE gkz IN (2,4,5,6,10);
UPDATE gemeinden SET aa=3 WHERE gkz IN (12,14,15,16);
UPDATE gemeinden SET aa=51 WHERE gkz IN (17,18);
UPDATE gemeinden SET aa=62 WHERE gkz IN (19,20);
SELECT * FROM gemeinden;

PROMPT Aufgabe 2 c)
SELECT aa, AVG(al), MAX(al), MIN(al) FROM gemeinden
   HAVING aa BETWEEN 51 AND 62
   GROUP BY aa;


PROMPT Aufgabe 3:
PROMPT Anlegen der Tabellen
DROP TABLE points;
CREATE TABLE points 
             (p_nr NUMBER, x NUMBER, y number);
INSERT INTO points VALUES( 1, 11, 45);
INSERT INTO points VALUES( 2, 26, 66);
INSERT INTO points VALUES( 3, 24, 35);
INSERT INTO points VALUES( 4, 146, 44);
INSERT INTO points VALUES( 5, 67, 119);
INSERT INTO points VALUES( 7, 46, 44);
INSERT INTO points VALUES( 9, 67, 19);
SELECT * FROM points;

DROP TABLE lines;
CREATE TABLE lines 
             (l_nr NUMBER, anf NUMBER, end number);
INSERT INTO lines VALUES( 1, 1, 2);
INSERT INTO lines VALUES( 2, 7, 2);
INSERT INTO lines VALUES( 3, 1, 3);
INSERT INTO lines VALUES( 4, 1, 7);
INSERT INTO lines VALUES( 5, 9, 7);
INSERT INTO lines VALUES( 7, 3, 7);
INSERT INTO lines VALUES( 6, 3, 9);
SELECT * FROM lines;

DROP TABLE polygons;
CREATE TABLE polygons 
             (p_nr NUMBER, l NUMBER);
INSERT INTO polygons VALUES( 1, 1);
INSERT INTO polygons VALUES( 1, 2);
INSERT INTO polygons VALUES( 1, 4);
INSERT INTO polygons VALUES( 2, 3);
INSERT INTO polygons VALUES( 2, 7);
INSERT INTO polygons VALUES( 2, 4);
INSERT INTO polygons VALUES( 17, 5);
INSERT INTO polygons VALUES( 17, 6);
INSERT INTO polygons VALUES( 17, 7);
SELECT * FROM polygons;

PROMPT Aufgabe 3 a)
SELECT l_nr FROM lines 
   WHERE anf=7 OR end=7;

PROMPT Aufgabe 3 b)
SELECT l_nr FROM lines 
   WHERE (anf=7 AND end=9) OR (anf=9 AND end=7);

PROMPT Aufgabe 3 c)
SELECT p_nr FROM points
  MINUS
SELECT anf FROM lines;

PROMPT Aufgabe 3 d)
SELECT p_nr FROM points 
   MINUS
(SELECT anf FROM lines
   UNION
SELECT end FROM lines);

PROMPT Aufgabe 3 e) 
SELECT * FROM points p WHERE p.p_nr IN 
   (SELECT anf FROM lines WHERE l_nr IN 
       (SELECT l FROM polygons WHERE p_nr=17))
                          OR p.p_nr IN 
   (SELECT end FROM lines WHERE l_nr IN 
       (SELECT l FROM polygons WHERE p_nr=17));