PROMPT Sommersemester 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 a)
PROMPT Anlegen der Tabelle haltestellen
DROP TABLE haltestellen;
CREATE TABLE haltestellen (nr_der_haltestelle NUMBER, name_der_haltestelle CHAR(20));
PROMPT Anlegen der Tabelle lh
DROP TABLE lh;
CREATE TABLE lh (linie CHAR(20), nummer_der_haltestelle NUMBER);

PROMPT Aufgabe 1 b)
INSERT INTO haltestellen VALUES( 64, 'Hauptbahnhof');
INSERT INTO haltestellen VALUES( 43, 'Ettlinger Tor');
INSERT INTO lh VALUES( 'S1', 43);
INSERT INTO lh VALUES( ' 4', 43);
SELECT * FROM haltestellen;
SELECT * FROM lh;

PROMPT Aufgabe 1 c)
SELECT * FROM haltestellen ORDER BY name_der_haltestelle;
PROMPT Aufgabe 1 d)
SELECT l.linie, h.name_der_haltestelle FROM lh l, haltestellen h
   WHERE l.nummer_der_haltestelle=h.nr_der_haltestelle
   ORDER BY l.linie;
PROMPT Aufgabe 1 e)
PROMPT Weniger elegant, da VIEW hilfe benutzt wird. 
DROP VIEW hilfe;
CREATE VIEW hilfe as SELECT nummer_der_haltestelle, COUNT(*) anzahl_der_linien 
   FROM lh
   GROUP BY nummer_der_haltestelle;
SELECT * FROM hilfe;
SELECT h.nr_der_haltestelle, h.name_der_haltestelle, l.anzahl_der_linien 
   FROM haltestellen h, hilfe l
   WHERE h.nr_der_haltestelle=l.nummer_der_haltestelle;

PROMPT So geht es auch! In der FROM-Klausel steht ein SELECT!
PROMPT Elegant, da ohne VIEW hilfe ausgekommen wird. 
SELECT h.nr_der_haltestelle, h.name_der_haltestelle, l.anzahl_der_linien 
   FROM haltestellen h, (SELECT nummer_der_haltestelle, COUNT(*) anzahl_der_linien 
                           FROM lh GROUP BY nummer_der_haltestelle) l
   WHERE h.nr_der_haltestelle=l.nummer_der_haltestelle;


PROMPT Aufgabe 2
PROMPT Anlegen der Tabelle laender_der_erde
DROP TABLE laender_der_erde;
CREATE TABLE laender_der_erde (name_des_landes CHAR(20),
                               pke NUMBER(10,2));
INSERT INTO laender_der_erde VALUES( 'Argentinien', 2140);
INSERT INTO laender_der_erde VALUES( 'Brasilien', 1004);
INSERT INTO laender_der_erde VALUES( 'USA', 11386);
INSERT INTO laender_der_erde VALUES( 'Deutschland', 5450);
SELECT * FROM laender_der_erde;


PROMPT Aufgabe 2 a)
SELECT MIN(pke), MAX(pke), AVG(pke) FROM laender_der_erde;

PROMPT Aufgabe 2 b)
SELECT name_des_landes, pke FROM laender_der_erde
   WHERE pke = (SELECT MIN(pke) FROM laender_der_erde);

PROMPT Aufgabe 2 c)
SELECT a.name_des_landes, b.name_des_landes, ABS(a.pke-b.pke)
   FROM laender_der_erde a, laender_der_erde b
   WHERE a.pke-b.pke = (
      SELECT MAX(ABS(a.pke-b.pke)) FROM laender_der_erde a, laender_der_erde b)
      OR a.pke-b.pke = (
      SELECT MIN(ABS(a.pke-b.pke)) FROM laender_der_erde a, laender_der_erde b
             WHERE a.name_des_landes<>b.name_des_landes);


PROMPT Aufgabe 3
PROMPT Anlegen der Tabelle koordinaten
DROP TABLE koordinaten;
CREATE TABLE koordinaten (ort CHAR(10), rechtswert number, hochwert number);
INSERT INTO koordinaten VALUES ('Karlsruhe',3456, 5431);
INSERT INTO koordinaten VALUES ('Ettlingen',3451, 5424);
INSERT INTO koordinaten VALUES ('Rastatt',3462, 5471);
INSERT INTO koordinaten VALUES ('Mannheim',3419, 5444);
INSERT INTO koordinaten VALUES ('Bruchsal',3462, 5439);
SELECT * FROM koordinaten;


PROMPT Aufgabe 3 a)
SELECT ort, SQRT(POWER(rechtswert-3456,2)+POWER(hochwert-5431,2)) "Entfernung" FROM koordinaten
   WHERE SQRT(POWER(rechtswert-3456,2)+POWER(hochwert-5431,2)) < 30 AND ort<>'Karlsruhe';

PROMPT Aufgabe 3 b)
SELECT 'Entfernung von Karlsruhe nach ', ort, SQRT(POWER(rechtswert-3456,2)+POWER(hochwert-5431,2)) "KM"
   FROM koordinaten
   WHERE (rechtswert, hochwert)=(SELECT rechtswert, hochwert FROM koordinaten
                                  WHERE ort='Bruchsal');

PROMPT Aufgabe 3 c)
SELECT ort, SQRT(POWER(rechtswert-3456,2)+POWER(hochwert-5431,2)) "KM"
   FROM koordinaten
   WHERE SQRT(POWER(rechtswert-3456,2)+POWER(hochwert-5431,2)) >
      (SELECT SQRT(POWER(rechtswert-3456,2)+POWER(hochwert-5431,2)) FROM koordinaten 
          WHERE ort='Bruchsal');

PROMPT Aufgabe 3 d)
SELECT a.ort, b.ort, SQRT(POWER(a.rechtswert-b.rechtswert,2)+
                          POWER(a.hochwert-b.hochwert,2)) "Entfernung"
   FROM koordinaten a, koordinaten b WHERE a.ort<b.ort ORDER BY a.ort, b.ort;

PROMPT Aufgabe 4
PROMPT Anlegen der Tabellen tab_a, tab_b und tab_c
DROP TABLE tab_a;
CREATE TABLE tab_a 
             (col_a1 CHAR(2), col_a2 CHAR(2), col_a3 CHAR(2));
INSERT INTO tab_a VALUES('a1','a2','a3');
SELECT * FROM tab_a;

DROP TABLE tab_b;
CREATE TABLE tab_b 
             (col_b1 CHAR(2), col_b2 CHAR(2), col_b3 CHAR(2));
INSERT INTO tab_b VALUES('b1','b2','b3');
SELECT * FROM tab_b;

DROP TABLE tab_c;
CREATE TABLE tab_c 
             (col_c1 CHAR(2), col_c2 CHAR(2), col_c3 CHAR(2));
INSERT INTO tab_c VALUES('c1','c2','c3');
SELECT * FROM tab_c;

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 5
PROMPT Anlegen der Tabellen gemeinden
DROP TABLE gemeinden;
CREATE TABLE gemeinden 
             (gkz NUMBER, pkw 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);
SELECT * FROM gemeinden;

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

PROMPT Aufgabe 5 b)
UPDATE gemeinden SET akz='KA' WHERE gkz IN (1,3,7,8,9,11,13);
UPDATE gemeinden SET akz='MA' WHERE gkz IN (2,4,5,6,10);
UPDATE gemeinden SET akz='FR' WHERE gkz IN (12,14,15,16);
SELECT * FROM gemeinden;

PROMPT Aufgabe 5 c)
SELECT akz, AVG(pkw), MAX(pkw), MIN(pkw) FROM gemeinden
   GROUP BY akz;

PROMPT Aufgabe 6
PROMPT Anlegen der Tabellen strassen, gaststaetten und point_of_interest
DROP TABLE strassen;
CREATE TABLE strassen 
             (name_der_strasse CHAR(30));
INSERT INTO strassen VALUES( 'Moltkestraße');
INSERT INTO strassen VALUES( 'Erzberger Straße');
INSERT INTO strassen VALUES( 'Straße des Roten Kreuzes');
INSERT INTO strassen VALUES( 'Lange Straße');
INSERT INTO strassen VALUES( 'Kiefernweg');
INSERT INTO strassen VALUES( 'Kaiserallee');
INSERT INTO strassen VALUES( 'Am Wald');
SELECT * FROM strassen;

DROP TABLE gaststaetten;
CREATE TABLE gaststaetten 
             (name_der_gaststaette CHAR(30), strasse CHAR(30), hausnummer CHAR(30));
INSERT INTO gaststaetten VALUES( 'Mama Leone','Kiefernweg','2');
INSERT INTO gaststaetten VALUES( 'Kaisergarten','Kaiserallee','23');
INSERT INTO gaststaetten VALUES( 'Koffler"s Heuriger','Langestraße','1');
INSERT INTO gaststaetten VALUES( 'San Felice','Am Wald','3');
SELECT * FROM gaststaetten;

DROP TABLE point_of_interest;
CREATE TABLE point_of_interest 
             (name_der_sehenswuerdigkeit CHAR(30), strasse CHAR(30), hausnummer CHAR(30));
INSERT INTO point_of_interest VALUES( 'Hochschule Karlsruhe','Moltkestraße','30');
INSERT INTO point_of_interest VALUES( 'Alter Flughafen','Erzbergerstraße',' ');
INSERT INTO point_of_interest VALUES( 'Universität','Keiserallee','12');
SELECT * FROM point_of_interest;

PROMPT Aufgabe 6 a)
DROP TABLE allestrassen;
CREATE TABLE allestrassen(name_der_strasse, quelle) AS
   (SELECT name_der_strasse, 's' FROM strassen)
   UNION
   (SELECT strasse, 'g' FROM gaststaetten)
   UNION
   (SELECT strasse, 'p' FROM point_of_interest);
SELECT * FROM allestrassen;
   
PROMPT Aufgabe 6 b)
((SELECT strasse FROM gaststaetten)
   UNION
   (SELECT strasse FROM point_of_interest))
   MINUS
   (SELECT name_der_strasse FROM strassen);

PROMPT Aufgabe 6 a)
SELECT * FROM strassen
   WHERE NOT( name_der_strasse LIKE '_%straße%' 
   OR name_der_strasse LIKE '_%Straße%');