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

PROMPT Aufgabe 1 a)
ALTER TABLE bahnhof_linie ADD (ubhf NUMBER(1));
UPDATE bahnhof_linie SET ubhf = 0;
UPDATE bahnhof_linie SET ubhf = 1 WHERE bhf_nr IN (
   SELECT bhf_nr FROM bahnhof_linie HAVING COUNT(bhf_nr) > 1 GROUP BY bhf_nr);
SELECT * FROM bahnhof_linie;

PROMPT Aufgabe 1 b)
SELECT bhf_nr FROM bahnhof_linie WHERE linie_nr = 3;
SELECT linie_nr FROM bahnhof_linie WHERE bhf_nr = 7;

PROMPT Aufgabe 1 c)
SELECT bhf_nr FROM bahnhof_linie WHERE linie_nr IN (
   SELECT linie_nr FROM bahnhof_linie WHERE bhf_nr IN (
      SELECT bhf_nr FROM bahnhof_linie WHERE ubhf = 1 and linie_nr IN (
         SELECT linie_nr FROM bahnhof_linie WHERE bhf_nr = 13)));



PROMPT Aufgabe 2
PROMPT Anlegen der Tabellen
DROP TABLE tabelle;
CREATE TABLE tabelle (gemeinde CHAR(8), x NUMBER(5,2));
INSERT INTO tabelle VALUES('08145123',40.2);
INSERT INTO tabelle VALUES('08211000',120.0);
INSERT INTO tabelle VALUES('08312111',200.5);
INSERT INTO tabelle VALUES('08411000',27.2);
INSERT INTO tabelle VALUES('08212137',64.7);
SELECT * FROM tabelle;

PROMPT Aufgabe 2 a)
ALTER TABLE tabelle ADD (klasse NUMBER(1));

PROMPT Aufgabe 2 b)
UPDATE tabelle SET klasse = 3
   WHERE x > 1.3 * (SELECT AVG(x) FROM tabelle);
UPDATE tabelle SET klasse = 2
   WHERE x < 1.3 * (SELECT AVG(x) FROM tabelle);
UPDATE tabelle SET klasse = 1
   WHERE x < 0.7 * (SELECT AVG(x) FROM tabelle);
SELECT * FROM tabelle;


PROMPT Aufgabe 2 c)
SELECT Klasse FROM tabelle GROUP BY klasse;

PROMPT Aufgabe 2 d)
SELECT klasse, count(*) FROM tabelle GROUP BY klasse;



PROMPT Aufgabe 3
PROMPT Anlegen der Tabellen
DROP TABLE tabelle;
CREATE TABLE tabelle (gemeinde CHAR(8), 
                      a NUMBER(5,2),
                      b NUMBER(5,2),
                      c NUMBER(5,2),
                      d NUMBER(5,2),
                      arbbez NUMBER(3));
INSERT INTO tabelle VALUES('08145123',40.0,60.0,20.0,100.0,1);
INSERT INTO tabelle VALUES('08211000',100.0,50.0,40.0,300.0,1);
INSERT INTO tabelle VALUES('08312111',200.0,200.0,100.0,40.0,2);
INSERT INTO tabelle VALUES('08411000',27.2,36.7,123.5,22.7,2);
INSERT INTO tabelle VALUES('08212137',64.7,56.4,33.8,175.7,2);
SELECT * FROM tabelle;

PROMPT Aufgabe 3 a)
SELECT MAX(a) maxim FROM tabelle;
SELECT a/200*8 la, b/200*8 lb, c/200*8 lc, d/200*8 ld FROM tabelle;

PROMPT Aufgabe 3 b)
SELECT AVG(a)/200*8 la, AVG(b)/200*8 lb, AVG(c)/200*8 lc, AVG(d)/200*8 ld 
       FROM tabelle GROUP BY arbbez;