PROMPT Wintersemester 2006
PROMPT Leistungsnachweis K5D42
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 dichter
DROP TABLE dichter;
CREATE TABLE dichter (name CHAR(18), geburtstag DATE);
INSERT INTO dichter VALUES ('Friedrich Schiller','10.11.1759');
INSERT INTO dichter VALUES ('Wolfgang Goethe','28.8.1749');
SELECT * FROM dichter;
SELECT * FROM dichter WHERE name LIKE '%Schiller';
SELECT * FROM dichter WHERE name LIKE '%Goethe';

PROMPT Aufgabe 1 a)
PROMPT Das ist zu einfach, um hier gezeigt zu werden.


PROMPT Aufgabe 1 b)
PROMPT 'Friedrich Schiller' ist genau 18 Zeichen lang, 
PROMPT während 'Wolfgang Goethe' kürzer ist. 
PROMPT Mit LIKE '%Goethe%' wäre das dritte SELECT nicht leer.

PROMPT Aufgabe 1 c)
PROMPT nicht elegant:
SELECT * FROM dichter
   WHERE geburtstag BETWEEN '28.8.1749' AND '10.11.1759';
PROMPT elegant, weil man nur die Namen der beiden Dichter zu kennen braucht:
SELECT * FROM dichter
   WHERE geburtstag BETWEEN (SELECT geburtstag FROM dichter WHERE name LIKE '%Goethe%')
   AND (SELECT geburtstag FROM dichter WHERE name LIKE '%Schiller%');

PROMPT Aufgabe 2
PROMPT Anlegen der Tabellen werte1 und werte2
DROP TABLE werte1;
CREATE TABLE werte1 (lfd NUMBER(3), s11 NUMBER(3), s12 NUMBER(3));
INSERT INTO werte1 VALUES (1, 1, 4);
INSERT INTO werte1 VALUES (2, 1, 3);
INSERT INTO werte1 VALUES (3, 2, 2);
INSERT INTO werte1 VALUES (4, 2, 1);
INSERT INTO werte1 VALUES (5, 2, 4);
INSERT INTO werte1 VALUES (6, 2, 3);
INSERT INTO werte1 VALUES (7, 1, 2);
INSERT INTO werte1 VALUES (8, 1, 1);
SELECT * FROM werte1;

DROP TABLE werte2;
CREATE TABLE werte2 (lfd NUMBER(3), s21 NUMBER(3), s22 NUMBER(3));
INSERT INTO werte2 VALUES (1, 1, 4);
INSERT INTO werte2 VALUES (2, 3, 3);
INSERT INTO werte2 VALUES (3, 2, 2);
SELECT * FROM werte2;

PROMPT Aufgabe 2 a)
SELECT * FROM werte1
   WHERE s11=2 AND s12 IN (3, 4);

PROMPT Aufgabe 2 b)
SELECT * FROM werte1 ORDER BY s12 ASC, s11 DESC;

PROMPT Aufgabe 2 c)
SELECT * FROM werte1, werte2 WHERE s12=s21;

PROMPT Aufgabe 3
PROMPT Anlegen der Tabelle gemeinden und ags_ds
DROP TABLE gemeinden;
CREATE TABLE gemeinden (ags CHAR(8), name CHAR(40), erw NUMBER, al NUMBER);
INSERT INTO gemeinden VALUES ('08212000','Karlsruhe, Stadt', 90464,12828);
INSERT INTO gemeinden VALUES ('08215089','Walzbachtal', 3325, 241);
INSERT INTO gemeinden VALUES ('08215090','Weingarten (Baden)', 3176, 236);
INSERT INTO gemeinden VALUES ('08215101','Pfinztal', 5959, 472);
INSERT INTO gemeinden VALUES ('08215102','Eggenstein-Leopoldshafen', 5703, 447);
INSERT INTO gemeinden VALUES ('08215105','Linkenheim-Hochstetten', 4146, 348);
INSERT INTO gemeinden VALUES ('08215108','Rheinstetten, Stadt', 7113, 585);
INSERT INTO gemeinden VALUES ('08215109','Stutensee, Stadt', 7765, 715);
INSERT INTO gemeinden VALUES ('08215111','Dettenheim', 2452, 192);
INSERT INTO gemeinden VALUES ('08215017','Ettlingen, Stadt', 12810, 1066);
INSERT INTO gemeinden VALUES ('08215046','Malsch', 4999, 363);
INSERT INTO gemeinden VALUES ('08215047','Marxzell', 1732, 107);
INSERT INTO gemeinden VALUES ('08215096','Karlsbad', 5818, 369);
INSERT INTO gemeinden VALUES ('08215110','Waldbronn', 4002, 252);
INSERT INTO gemeinden VALUES ('09000000','Nirgendwo', 4002, 252);
SELECT * FROM gemeinden;

DROP TABLE ags_ds;
CREATE TABLE ags_ds (ags CHAR(8), ds CHAR(5));
INSERT INTO ags_ds VALUES ('08212000','63101');
INSERT INTO ags_ds VALUES ('08215089','63101');
INSERT INTO ags_ds VALUES ('08215090','63101');
INSERT INTO ags_ds VALUES ('08215101','63101');
INSERT INTO ags_ds VALUES ('08215102','63101');
INSERT INTO ags_ds VALUES ('08215105','63101');
INSERT INTO ags_ds VALUES ('08215108','63101');
INSERT INTO ags_ds VALUES ('08215109','63101');
INSERT INTO ags_ds VALUES ('08215111','63101');
INSERT INTO ags_ds VALUES ('08215017','63113');
INSERT INTO ags_ds VALUES ('08215046','63113');
INSERT INTO ags_ds VALUES ('08215047','63113');
INSERT INTO ags_ds VALUES ('08215096','63113');
INSERT INTO ags_ds VALUES ('08215110','63113');
INSERT INTO ags_ds VALUES ('09000000','70000');
SELECT * FROM ags_ds;

PROMPT Aufgabe 3 a)
ALTER TABLE gemeinden ADD (ds CHAR(5));

PROMPT Aufgabe 3 b)
UPDATE gemeinden SET ds = '63101' WHERE ags IN ('08212000',
   '08215089','08215090','08215101','08215102','08215105',
   '08215108','08215109','08215111');
UPDATE gemeinden SET ds = '63113' WHERE ags IN ('08215017',
   '08215046','08215047','08215096','08215110');
UPDATE gemeinden SET ds = '70000' WHERE ags IN ('09000000');
SELECT * FROM gemeinden;

PROMPT Aufgabe 3 b) Alternative
UPDATE gemeinden SET ds=(SELECT ds FROM ags_ds WHERE ags_ds.ags=gemeinden.ags);
SELECT * FROM gemeinden;

PROMPT Aufgabe 3 c)
SELECT AVG(al), MAX(al), MIN(al) FROM gemeinden
   WHERE ags LIKE '08%'
   GROUP BY ds;

PROMPT Aufgabe 3 c) Alternative
SELECT AVG(al), MAX(al), MIN(al) FROM gemeinden
   WHERE ags BETWEEN '08000000' AND '08999999'
   GROUP BY ds;

PROMPT Aufgabe 3 c) Alternative
SELECT AVG(al), MAX(al), MIN(al) FROM gemeinden
   WHERE SUBSTR(ags,1,2)='08'
   GROUP BY ds;

PROMPT Aufgabe 4
PROMPT Anlegen der Tabellen knoten, segmente und maschen
DROP TABLE knoten;
CREATE TABLE knoten 
             (id NUMBER, x_koor NUMBER, y_koor number);
INSERT INTO knoten VALUES( 1, 11, 45);
INSERT INTO knoten VALUES( 2, 26, 66);
INSERT INTO knoten VALUES( 3, 24, 35);
INSERT INTO knoten VALUES( 4, 146, 44);
INSERT INTO knoten VALUES( 5, 67, 119);
INSERT INTO knoten VALUES( 12, 46, 44);
INSERT INTO knoten VALUES( 17, 67, 19);
SELECT * FROM knoten;

DROP TABLE segmente;
CREATE TABLE segmente 
             (id NUMBER, von_knoten NUMBER, nach_knoten number);
INSERT INTO segmente VALUES( 1, 2, 3);
INSERT INTO segmente VALUES( 2, 1, 2);
INSERT INTO segmente VALUES( 3, 3, 4);
INSERT INTO segmente VALUES( 4, 3, 1);
INSERT INTO segmente VALUES( 5, 17, 12);
INSERT INTO segmente VALUES( 7, 3, 7);
INSERT INTO segmente VALUES( 6, 3, 9);
SELECT * FROM segmente;

DROP TABLE maschen;
CREATE TABLE maschen 
             (id NUMBER, segment NUMBER);
INSERT INTO maschen VALUES( 1, 4);
INSERT INTO maschen VALUES( 1, 2);
INSERT INTO maschen VALUES( 1, 1);
INSERT INTO maschen VALUES( 2, 3);
INSERT INTO maschen VALUES( 2, 7);
INSERT INTO maschen VALUES( 2, 4);
INSERT INTO maschen VALUES( 17, 5);
INSERT INTO maschen VALUES( 17, 6);
INSERT INTO maschen VALUES( 17, 7);
SELECT * FROM maschen;

PROMPT Aufgabe 4 a)
SELECT * FROM segmente 
   WHERE von_knoten=3 OR nach_knoten=3;

PROMPT Aufgabe 4 b)
SELECT s.id, k1.x_koor, k1.y_koor, k2.x_koor, k2.y_koor 
   FROM segmente s, knoten k1, knoten k2
   WHERE s.von_knoten=k1.id AND s.nach_knoten=k2.id; 

PROMPT Aufgabe 4 c)
SELECT DISTINCT k.id FROM maschen m, segmente s, knoten k
   WHERE m.segment=s.id AND (s.von_knoten=k.id OR s.nach_knoten=k.id)
   AND m.id=1;

PROMPT Aufgabe 4 c) Alternative
SELECT DISTINCT nach_knoten FROM segmente WHERE id IN
   (SELECT nach_knoten FROM segmente WHERE id IN
      (SELECT segment FROM maschen WHERE id=1))
UNION
   (SELECT von_knoten FROM segmente WHERE id IN
      (SELECT segment FROM maschen WHERE id=1));


PROMPT Aufgabe 5
PROMPT Anlegen der Tabellen linien, haltestellen und l_h
DROP TABLE linien;
CREATE TABLE linien(id NUMBER(3), name CHAR(10));
INSERT INTO linien VALUES(1,'Linie 1');
INSERT INTO linien VALUES(2,'Linie 2');
INSERT INTO linien VALUES(3,'Linie 3');
INSERT INTO linien VALUES(4,'Linie 4');
INSERT INTO linien VALUES(5,'Linie 5');
SELECT * FROM linien;

DROP TABLE haltestellen;
CREATE TABLE haltestellen(id NUMBER(3), name CHAR(20));
INSERT INTO haltestellen VALUES(11,'Haltestelle 11');
INSERT INTO haltestellen VALUES(12,'Haltestelle 12');
INSERT INTO haltestellen VALUES(13,'Haltestelle 13');
INSERT INTO haltestellen VALUES(14,'Haltestelle 14');
INSERT INTO haltestellen VALUES(15,'Haltestelle 15');
INSERT INTO haltestellen VALUES(16,'Haltestelle 16');
INSERT INTO haltestellen VALUES(17,'Haltestelle 17');
INSERT INTO haltestellen VALUES(21,'Haltestelle 21');
INSERT INTO haltestellen VALUES(22,'Haltestelle 22');
INSERT INTO haltestellen VALUES(23,'Haltestelle 23');
INSERT INTO haltestellen VALUES(24,'Haltestelle 24');
INSERT INTO haltestellen VALUES(25,'Haltestelle 25');
INSERT INTO haltestellen VALUES(26,'Haltestelle 26');
INSERT INTO haltestellen VALUES(31,'Haltestelle 31');
INSERT INTO haltestellen VALUES(32,'Haltestelle 32');
INSERT INTO haltestellen VALUES(33,'Haltestelle 33');
INSERT INTO haltestellen VALUES(34,'Haltestelle 34');
INSERT INTO haltestellen VALUES(35,'Haltestelle 35');
INSERT INTO haltestellen VALUES(36,'Haltestelle 36');
INSERT INTO haltestellen VALUES(41,'Haltestelle 41');
INSERT INTO haltestellen VALUES(42,'Haltestelle 42');
INSERT INTO haltestellen VALUES(43,'Haltestelle 43');
INSERT INTO haltestellen VALUES(44,'Haltestelle 44');
INSERT INTO haltestellen VALUES(51,'Haltestelle 51');
INSERT INTO haltestellen VALUES(52,'Haltestelle 52');
INSERT INTO haltestellen VALUES(53,'Haltestelle 53');
INSERT INTO haltestellen VALUES(54,'Haltestelle 54');
SELECT * FROM haltestellen;

DROP TABLE l_h;
CREATE TABLE l_h(lid NUMBER(3), hid NUMBER(3));
INSERT INTO l_h VALUES(1,11);
INSERT INTO l_h VALUES(1,12);
INSERT INTO l_h VALUES(1,13);
INSERT INTO l_h VALUES(1,14);
INSERT INTO l_h VALUES(1,15);
INSERT INTO l_h VALUES(1,16);
INSERT INTO l_h VALUES(1,17);
INSERT INTO l_h VALUES(2,21);
INSERT INTO l_h VALUES(2,22);
INSERT INTO l_h VALUES(2,15);
INSERT INTO l_h VALUES(2,23);
INSERT INTO l_h VALUES(2,24);
INSERT INTO l_h VALUES(2,25);
INSERT INTO l_h VALUES(2,26);
INSERT INTO l_h VALUES(3,31);
INSERT INTO l_h VALUES(3,32);
INSERT INTO l_h VALUES(3,13);
INSERT INTO l_h VALUES(3,33);
INSERT INTO l_h VALUES(3,34);
INSERT INTO l_h VALUES(3,35);
INSERT INTO l_h VALUES(3,36);
INSERT INTO l_h VALUES(4,41);
INSERT INTO l_h VALUES(4,42);
INSERT INTO l_h VALUES(4,24);
INSERT INTO l_h VALUES(4,43);
INSERT INTO l_h VALUES(4,44);
INSERT INTO l_h VALUES(5,51);
INSERT INTO l_h VALUES(5,52);
INSERT INTO l_h VALUES(5,34);
INSERT INTO l_h VALUES(5,53);
INSERT INTO l_h VALUES(5,54);
SELECT * FROM l_h;

PROMPT Aufgabe 5 a)
SELECT name FROM linien;
PROMPT Geben Sie den Namen einer Linie ein:;
ACCEPT mylinienname;

PROMPT Aufgabe 5 b)
SELECT l.name, h.name FROM linien l, haltestellen h, l_h
   WHERE l.name=&mylinienname AND l.id=l_h.lid AND l_h.hid=h.id;

PROMPT Aufgabe 5 c)
SELECT l1.name, l2.name FROM linien l1, linien l2
   WHERE l1.name=&mylinienname AND l2.name<>&mylinienname AND l2.id IN 
   (SELECT l_h.lid FROM l_h 
      WHERE l_h.hid IN
      (SELECT l_h.hid FROM l_h 
         WHERE l_h.lid=l1.id));

PROMPT Aufgabe 6
PROMPT Anlegen der Tabelle dateisystem
DROP TABLE dateisystem;
CREATE TABLE dateisystem(dateiname CHAR(30), dateityp CHAR(1), index1 NUMBER, index2 NUMBER);
INSERT INTO dateisystem VALUES('.public_html', 'd', 1689621, 543775);
INSERT INTO dateisystem VALUES('Welcome.shtml', '-', 1689622, 1689621);
INSERT INTO dateisystem VALUES('documents', 'd', 1695763, 1689621);
INSERT INTO dateisystem VALUES('Welcome.shtml', '-', 1695792, 1695763);
INSERT INTO dateisystem VALUES('diplomarbeiten', 'd', 1695764, 1695763);
INSERT INTO dateisystem VALUES('lehre', 'd', 1696768, 1695763);
INSERT INTO dateisystem VALUES('Welcome.shtml', '-', 1696814, 1696768);
INSERT INTO dateisystem VALUES('klausuren', 'd', 29749, 1696768);
INSERT INTO dateisystem VALUES('KlausurK5D42 DB+IS 2006WS.doc', '-', 29990, 29749);
INSERT INTO dateisystem VALUES('KlausurK5D42 DB+IS 2006WS.txt', '-', 29934, 29749);
INSERT INTO dateisystem VALUES('Welcome.shtml', '-', 29925, 29749);
SELECT * FROM dateisystem;

PROMPT Aufgabe 6 a)
PROMPT Weniger elegant, da VIEW hilfe benutzt wird. 
DROP VIEW hilfe;
CREATE VIEW hilfe 
   as SELECT index2, COUNT(*) anzahl FROM dateisystem
   GROUP BY index2;
SELECT * FROM hilfe;
SELECT d1.dateiname, d2.anzahl FROM dateisystem d1, hilfe d2
   WHERE d1.index1=d2.index2;

PROMPT Elegant, da ohne VIEW hilfe ausgekommen wird. 
SELECT d1.dateiname, d2.anzahl FROM dateisystem d1, 
   (SELECT index2, COUNT(*) anzahl FROM dateisystem GROUP BY index2) d2
   WHERE d1.index1=d2.index2;
   
    
     
PROMPT Aufgabe 6 b)
SELECT d1.dateiname, d2.dateiname FROM dateisystem d1, dateisystem d2
   WHERE d1.index1=d2.index2;
   
PROMPT Aufgabe 6 c)
SELECT dateiname FROM dateisystem WHERE index1=
   (SELECT index2 FROM dateisystem WHERE index1=
      (SELECT index2 FROM dateisystem WHERE dateiname='KlausurK5D42 DB+IS 2006WS.doc'));