PROMPT Sommersemester 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 a;
CREATE TABLE a (name_der_gemeinde CHAR(20),
                            statistische_kennziffer CHAR(8));
INSERT INTO a VALUES('Karlsruhe','08211000');
INSERT INTO a VALUES('Ettlingen','08215023');
SELECT * FROM a;

DROP TABLE b;
CREATE TABLE b (statistische_kennziffer CHAR(8),
                             rechtswert NUMBER(10),
                             hochwert NUMBER(10));
INSERT INTO b VALUES('08211000',3456000,5431000);
INSERT INTO b VALUES('08215023',3450400,5431000);
SELECT * FROM b;


PROMPT Aufgabe 1 a)
DROP TABLE c;
CREATE TABLE c AS
   SELECT name_der_gemeinde, rechtswert, hochwert
      FROM a, b  
      WHERE a.statistische_kennziffer = b.statistische_kennziffer;
SELECT * FROM c;

PROMPT Aufgabe 1 b)
DROP TABLE d;
CREATE TABLE d (name_der_gemeinde1, rechtswert1, hochwert1,
                name_der_gemeinde2, rechtswert2, hochwert2) AS
   SELECT c1.name_der_gemeinde, c1.rechtswert, c1.hochwert,
          c2.name_der_gemeinde, c2.rechtswert, c2.hochwert
          FROM c c1, c c2;
SELECT * FROM d;

PROMPT Aufgabe 1 c)
DROP TABLE e;
CREATE TABLE e (name_der_gemeinde1, name_der_gemeinde2, entfernung) AS
   SELECT name_der_gemeinde1, name_der_gemeinde2,
          SQRT(POWER(rechtswert1-rechtswert2,2) + 
               POWER(hochwert1-hochwert2,2))
          FROM d;
SELECT * FROM e;

PROMPT Aufgabe 1 e)
DROP TABLE e;
CREATE TABLE e (name_der_gemeinde1, name_der_gemeinde2, entfernung) AS
   SELECT name_der_gemeinde1, name_der_gemeinde2,
          SQRT(POWER(rechtswert1-rechtswert2,2) + 
               POWER(hochwert1-hochwert2,2))
          FROM d
          WHERE name_der_gemeinde1 < name_der_gemeinde2;
SELECT * FROM e;


PROMPT Aufgabe 2
PROMPT Anlegen der Tabellen
DROP TABLE bevoelkerung;
CREATE TABLE bevoelkerung (land CHAR(2),
                           reg_bez CHAR(1),
                           kreis CHAR(2),
                           gem CHAR(3),
                           bevoelkerung NUMBER(6));
INSERT INTO bevoelkerung VALUES('08','2','15','047',1600);
INSERT INTO bevoelkerung VALUES('08','2','15','046',5200);
INSERT INTO bevoelkerung VALUES('08','2','15','045',2900);
INSERT INTO bevoelkerung VALUES('08','2','16','031',7600);
INSERT INTO bevoelkerung VALUES('08','2','16','032',2800);
INSERT INTO bevoelkerung VALUES('08','1','11','000',20900);
INSERT INTO bevoelkerung VALUES('09','3','35','017',32900);
SELECT * FROM bevoelkerung;

PROMPT Aufgabe 2 a)
DROP TABLE reg_bez_ka;
CREATE TABLE reg_bez_ka AS
   SELECT * FROM bevoelkerung WHERE land = '08' AND reg_bez = '2';
SELECT * FROM reg_bez_ka;

PROMPT Aufgabe 2 b)
SELECT kreis, COUNT(*), SUM(bevoelkerung), MIN(bevoelkerung), MAX(bevoelkerung)
   FROM reg_bez_ka GROUP BY kreis;



PROMPT Aufgabe 3
PROMPT Anlegen der Tabellen
DROP TABLE mitarbeiter;
CREATE TABLE mitarbeiter (name CHAR(10),
                          nr_m NUMBER(3),
                          nr_v NUMBER(3));
INSERT INTO mitarbeiter VALUES('Boss', 1, 0);
INSERT INTO mitarbeiter VALUES('Meister', 2, 1);
INSERT INTO mitarbeiter VALUES('Geselle', 3, 2);
INSERT INTO mitarbeiter VALUES('Techniker', 4, 1);
INSERT INTO mitarbeiter VALUES('Azubi', 5, 4);
SELECT * FROM mitarbeiter;

PROMPT Aufgabe 3 a)
SELECT a.name "Name des Mitarbeiters", b.name "Name des Vorgesetzten"
   FROM mitarbeiter a, mitarbeiter b
   WHERE a.nr_v = b.nr_m;

PROMPT Aufgabe 3 b)
SELECT a.name "Name des Mitarbeiters", b.name "Name des Vorgesetzten"
   FROM mitarbeiter a, mitarbeiter b
   WHERE b.nr_m = (SELECT nr_v FROM mitarbeiter WHERE name = a.name);