PROMPT Sommersemester 2001
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 Tabelle mitarbeiter
DROP TABLE mitarbeiter;
CREATE TABLE mitarbeiter (name CHAR(20), telefon CHAR(15));
INSERT INTO mitarbeiter VALUES ('Mustermann','0721-987654');
INSERT INTO mitarbeiter VALUES ('Musterfrau','0721-981234');
SELECT * FROM mitarbeiter;

PROMPT Die Datei knifflig.sql
SET VERIFY OFF
SET TERMOUT OFF
SET HEADING OFF
SPOOL n:\DB\20010718P2\Aufgabe1\kniff.lst
SELECT 'DEFINE telefon = ' || telefon FROM mitarbeiter WHERE name = 'Mustermann';
SPOOL OFF
SET HEADING ON
SET TERMOUT ON
START n:\DB\20010718P2\Aufgabe1\kniff.lst
PROMPT Die Telefonnummer von Mustermann ist &telefon
SET VERIFY ON

PROMPT Die von knifflig erzeugte Datei kniff.lst
DEFINE telefon = 0721-987654                                                    

PROMPT Die Datei einfach.sql
PROMPT Wessen Telefonnummer suchen Sie?
ACCEPT person
START n:\DB\20010718P2\Aufgabe1\knifflig_mod &person

PROMPT Die geaenderte Datei knifflig_mod.sql
SET VERIFY OFF
SET TERMOUT OFF
SET HEADING OFF
SPOOL n:\DB\20010718P2\Aufgabe1\kniff.lst
SELECT 'DEFINE telefon = ' || telefon FROM mitarbeiter WHERE name = '&1';
SPOOL OFF
SET HEADING ON
SET TERMOUT ON
START n:\DB\20010718P2\Aufgabe1\kniff.lst
PROMPT Die Telefonnummer von &1 ist &telefon
SET VERIFY ON
SET FLUSH OFF

UNDEFINE telefon
UNDEFINE person
undefine 1


PROMPT Aufgabe 2:
PROMPT Anlegen der Tabellen
DROP TABLE lieferanten;
CREATE TABLE lieferanten (name CHAR(20), strasse CHAR(20), plz CHAR(20), ort CHAR(20));
INSERT INTO lieferanten VALUES ('Liefermann','Liefermannstr. 20','75000','Liefermannstadt');
INSERT INTO lieferanten VALUES ('Lieferfrau','Lieferfraugasse. 17','76000','Lieferfraudorf');
INSERT INTO lieferanten VALUES ('Lieferlein','Lieferleinplatz 10','75100','Lieferleinweiler');
SELECT * FROM lieferanten;

DROP TABLE kunden;
CREATE TABLE kunden (name CHAR(20), strasse CHAR(20), plz CHAR(20), ort CHAR(20),kundeseit NUMBER);
INSERT INTO kunden VALUES ('Kundmann','Kundmannstr. 20','75000','Kundmanntadt',1998);
INSERT INTO kunden VALUES ('Kundfrau','Kundfraugasse. 17','76000','Kundfraudorf',1999);
INSERT INTO kunden VALUES ('Kundlein','Kundleinplatz 10','75100','Kundleinweiler',2000);
SELECT * FROM kunden;

DROP TABLE autoren;
CREATE TABLE autoren (name CHAR(20), strasse CHAR(20), plz CHAR(20), ort CHAR(20),karten NUMBER);
INSERT INTO autoren VALUES ('Autormann','Autormannstr. 20','75000','Autormannstadt',1);
INSERT INTO autoren VALUES ('Autorfrau','Autorfraugasse. 17','76000','Autorfraudorf',2);
INSERT INTO autoren VALUES ('Autlein','Autleinplatz 10','75100','Autleinweiler',3);
SELECT * FROM autoren;

PROMPT Anlegen der Tabelle mailings
DROP VIEW mailings;
CREATE VIEW mailings AS
   SELECT name, strasse, plz, ort FROM lieferanten WHERE plz BETWEEN 75000 AND 75999
      UNION
   SELECT name, strasse, plz, ort FROM kunden WHERE kundeseit >= 1999
      UNION
   SELECT name, strasse, plz, ort FROM autoren WHERE karten >= 2;

PROMPT Abrufen der mailings
SELECT * FROM mailings;


PROMPT Aufgabe 3:
PROMPT Anlegen der Tabellen
DROP TABLE segmente;
CREATE TABLE segmente (seg_ID NUMBER, G_l NUMBER, G_r NUMBER);
INSERT INTO segmente VALUES (1,1,0);
INSERT INTO segmente VALUES (2,2,0);
INSERT INTO segmente VALUES (3,3,0);
INSERT INTO segmente VALUES (4,4,0);
INSERT INTO segmente VALUES (5,5,0);
INSERT INTO segmente VALUES (6,6,0);
INSERT INTO segmente VALUES (7,1,7);
INSERT INTO segmente VALUES (8,1,2);
INSERT INTO segmente VALUES (9,2,7);
INSERT INTO segmente VALUES (10,2,3);
INSERT INTO segmente VALUES (11,7,8);
INSERT INTO segmente VALUES (12,3,8);
INSERT INTO segmente VALUES (13,4,8);
INSERT INTO segmente VALUES (14,3,4);
INSERT INTO segmente VALUES (15,5,8);
INSERT INTO segmente VALUES (16,5,6);
INSERT INTO segmente VALUES (17,6,7);
SELECT * FROM segmente;

DROP TABLE gemeindekreis;
CREATE TABLE gemeindekreis (gem_ID NUMBER, kreis_ID NUMBER);
INSERT INTO gemeindekreis VALUES (1,1);
INSERT INTO gemeindekreis VALUES (2,2);
INSERT INTO gemeindekreis VALUES (3,2);
INSERT INTO gemeindekreis VALUES (4,3);
INSERT INTO gemeindekreis VALUES (5,4);
INSERT INTO gemeindekreis VALUES (6,4);
INSERT INTO gemeindekreis VALUES (7,5);
INSERT INTO gemeindekreis VALUES (8,5);
SELECT * FROM gemeindekreis;

PROMPT Aufgabe 3 a)
ALTER TABLE segmente ADD (k_l NUMBER, k_r NUMBER);

PROMPT Aufgabe 3 b)
UPDATE segmente SET k_l = (SELECT kreis_id FROM gemeindekreis WHERE gem_id = g_l);
UPDATE segmente SET k_r = (SELECT kreis_id FROM gemeindekreis WHERE gem_id = g_r);
SELECT * FROM segmente;

PROMPT Aufgabe 3 c)
DELETE FROM segmente WHERE k_l = k_r;

PROMPT Aufgabe 3 d)
DROP VIEW temp;
CREATE VIEW temp AS
   SELECT seg_id, k_l FROM segmente
      UNION
   SELECT seg_id, k_r FROM segmente WHERE k_r <> NULL;
SELECT k_l, count(*) FROM temp GROUP BY k_l;