Übung Versicherungen

1. Übungen Versicherung

1.1. Creating Tables

create table V_VERTRAGSORT (
    ABSCHLUSSID number(9) primary key,
    ORT varchar(30),
    DATUM date
);

create table V_VERTRETER (
    VERTRETERID number(9) primary key ,
    NNAME varchar2(30),
    PLZ number,
    ORT varchar(30)
);

create table V_VERTRAG (
    VERTRAGSID number(9) primary key,
    folgevertrag varchar2(30),
    nochgueltig varchar2(4),
    VORGAENGERVERTRAG varchar2(30),
    ABSCHLUSSDATUM date,
    VERTRETERID number(9),
    constraint FK_VERTRETERID foreign key (VERTRETERID) references V_VERTRETER
);

create table V_VERTRAGSABSCHLUSS (
    VERTRAGSID number(9),
    ABSCHLUSSID number(9),
    VERTRAGSGROESSE_EURO number(9),
    constraint FK_V_VERTRAG foreign key (VERTRAGSID) references V_VERTRAG,
    constraint FK_V_VERTRAGSORT foreign key (ABSCHLUSSID) references V_VERTRAGSORT,
    constraint PK_V_VERTRAGSABSCHLUSS primary key (ABSCHLUSSID, VERTRAGSID)
);

drop table V_VERTRAGSABSCHLUSS;
drop table V_VERTRAG;
drop table V_VERTRAGSORT;
drop table V_VERTRETER;

2. Inserts

2.1. Change Date Format

alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';

2.2. Insert Script

alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';

INSERT INTO v_vertragsort
(ABSCHLUSSID,ORT,DATUM)
VALUES
(1,'LONDON','17-03-2001')
/
INSERT INTO v_vertragsort
(ABSCHLUSSID,ORT,DATUM)
VALUES
(2,'WIEN','23-05-2001')
/
INSERT INTO v_vertragsort
(ABSCHLUSSID,ORT,DATUM)
VALUES
(3,'PARIS','04-08-2001')
/
INSERT INTO v_vertragsort
(ABSCHLUSSID,ORT,DATUM)
VALUES
(4,'ROM','28-08-2001')
/

INSERT INTO v_vertreter
(VERTRETERID,NNAME,PLZ,ORT)
VALUES
(1,'Maier',9920,'ROM')
/
INSERT INTO v_vertreter
(VERTRETERID,NNAME,PLZ,ORT)
VALUES
(2,'Müller',5550,'LONDON')
/
INSERT INTO v_vertreter
(VERTRETERID,NNAME,PLZ,ORT)
VALUES
(3,'Huber',440,'WIEN')
/
INSERT INTO v_vertreter
(VERTRETERID,NNAME,PLZ,ORT)
VALUES
(4,'Bauer',6660,'PARIS')
/

INSERT INTO v_vertrag
(vertragsid,folgevertrag,nochgueltig,vorgaengervertrag,abschlussdatum,vertreterid)
VALUES
(1,'Vertrag1Haus','JA',NULL,NULL,'1')
/
INSERT INTO v_vertrag
(vertragsid,folgevertrag,nochgueltig,vorgaengervertrag,abschlussdatum,vertreterid)
VALUES
(2,'Vertrag2Auto','NEIN',NULL,NULL,'2')
/
INSERT INTO v_vertrag
(vertragsid,folgevertrag,nochgueltig,vorgaengervertrag,abschlussdatum,vertreterid)
VALUES
(3,'Vertrag3Auto','NEIN',NULL,NULL,'2')
/
INSERT INTO v_vertrag
(vertragsid,folgevertrag,nochgueltig,vorgaengervertrag,abschlussdatum,vertreterid)
VALUES
(4,'Vertrag4Auto','JA','Vertrag1Haus','02-01-1990','1')
/
INSERT INTO v_vertrag
(vertragsid,folgevertrag,nochgueltig,vorgaengervertrag,abschlussdatum,vertreterid)
VALUES
(5,'Vertrag5Auto','NEIN','Vertrag1Haus','15-07-1990','2')
/
INSERT INTO v_vertrag
(vertragsid,folgevertrag,nochgueltig,vorgaengervertrag,abschlussdatum,vertreterid)
VALUES
(6,'Vertrag6Lebens','JA','Vertrag1Haus','12-03-1991','3')
/
INSERT INTO v_vertrag
(vertragsid,folgevertrag,nochgueltig,vorgaengervertrag,abschlussdatum,vertreterid)
VALUES
(7,'Vertrag7Lebens','JA','Vertrag1Haus','22-08-1992','4')
/
INSERT INTO v_vertrag
(vertragsid,folgevertrag,nochgueltig,vorgaengervertrag,abschlussdatum,vertreterid)
VALUES
(8,'Vertrag8Lebens','JA','Vertrag4Auto','30-03-1993','1')
/
INSERT INTO v_vertrag
(vertragsid,folgevertrag,nochgueltig,vorgaengervertrag,abschlussdatum,vertreterid)
VALUES
(9,'Vertrag5AutoNE','JA','Vertrag4Auto','04-12-1993','2')
/
INSERT INTO v_vertrag
(vertragsid,folgevertrag,nochgueltig,vorgaengervertrag,abschlussdatum,vertreterid)
VALUES
(10,'Vertrag9Lebens','JA','Vertrag1Haus','09-02-1993','3')
/
INSERT INTO v_vertrag
(vertragsid,folgevertrag,nochgueltig,vorgaengervertrag,abschlussdatum,vertreterid)
VALUES
(11,'Vertrag10Lebens','NEIN','Vertrag7Lebens','24-07-1994','4')
/
INSERT INTO v_vertrag
(vertragsid,folgevertrag,nochgueltig,vorgaengervertrag,abschlussdatum,vertreterid)
VALUES
(12,'Vertrag11Lebens','NEIN','Vertrag4Auto','12-08-1995','4')
/
INSERT INTO v_vertrag
(vertragsid,folgevertrag,nochgueltig,vorgaengervertrag,abschlussdatum,vertreterid)
VALUES
(13,'Vertrag12Lebens','JA','Vertrag5AutoNE','03-04-1996','4')
/
INSERT INTO v_vertrag
(vertragsid,folgevertrag,nochgueltig,vorgaengervertrag,abschlussdatum,vertreterid)
VALUES
(14,'Vertrag13Lebens','JA','Vertrag5AutoNE','25-12-1996','2')
/
INSERT INTO v_vertrag
(vertragsid,folgevertrag,nochgueltig,vorgaengervertrag,abschlussdatum,vertreterid)
VALUES
(15,'Vertrag14Lebens','JA','Vertrag7Lebens','21-07-1996','2')
/
INSERT INTO v_vertrag
(vertragsid,folgevertrag,nochgueltig,vorgaengervertrag,abschlussdatum,vertreterid)
VALUES
(16,'Vertrag15Lebens','JA','Vertrag12Lebens','11-10-1998','3')
/
INSERT INTO v_vertragsabschluss
(VERTRAGSID,ABSCHLUSSID,VERTRAGSGROESSE_EURO)
VALUES
(11,1,500)
/
INSERT INTO v_vertragsabschluss
(VERTRAGSID,ABSCHLUSSID,VERTRAGSGROESSE_EURO)
VALUES
(15,1,1000)
/
INSERT INTO v_vertragsabschluss
(VERTRAGSID,ABSCHLUSSID,VERTRAGSGROESSE_EURO)
VALUES
(12,3,300)
/
INSERT INTO v_vertragsabschluss
(VERTRAGSID,ABSCHLUSSID,VERTRAGSGROESSE_EURO)
VALUES
(11,3,600)
/
INSERT INTO v_vertragsabschluss
(VERTRAGSID,ABSCHLUSSID,VERTRAGSGROESSE_EURO)
VALUES
(13,4,1000)
/
INSERT INTO v_vertragsabschluss
(VERTRAGSID,ABSCHLUSSID,VERTRAGSGROESSE_EURO)
VALUES
(14,4,300)
/

2.3. Commit changes

commit;

3. Abfragen

3.1. Abfrage

Zeige sämtliche Vertragsorte an sowie die Anzahl der dort abgeschlossenen Verträge. VERTRÄGE

select 'Am ' || to_char(DATUM, 'DD-MM-YYYY') || ' in ' || ORT || ' mit ' || count(VERTRAGSID)  || ' Vertraege abgeschlossen' as VERTRÄGE
from V_VERTRAGSABSCHLUSS
    full outer join V_VERTRAGSORT VV using (ABSCHLUSSID)
group by ORT, DATUM
order by DATUM;

3.2. Abfrage

Zeige zu jedem Vertreter dessen Verträge. Beachten, dass die Verträge hierarchisch dargestellt werden sollen. Das erste Ordnungskriterium ist die Vertragsgeneration, das zweite der Vertragsnamen. Hinweis: Da hierarchische Abfragen nur auf einzelnen Tabellen erlaubt sind, verwende eine Subquery als Spaltenausdruck für den Vertreter.

select (select nname from V_VERTRETER where VERTRETERID = v.VERTRETERID) vertrer, lpad(' ', 4 * (LEVEL -1)) || ' ' || FOLGEVERTRAG vertrag from V_VERTRAG v
connect by prior FOLGEVERTRAG = VORGAENGERVERTRAG
start with FOLGEVERTRAG = 'Vertrag1Haus'
order by LEVEL, VERTRAG

3.3. Abfrage

Zeige zu jedem Vertrag die Vertragsorte und die Provision. In dieser Aufteilung sollen auch jene Verträge angeführt werden, die keine Provision enthalten

select VERTRAGSID,
       FOLGEVERTRAG,
       nvl(ORT, '---') as ORT,
       nvl(VERTRAGSGROESSE_EURO, 0) || ' EURO Umsatz' as PROVISION,
       nvl(to_char(DATUM, 'DD-MM-YYYY'), '---') as Abschlussdatum
from V_VERTRAG
    left join V_VERTRAGSABSCHLUSS using (VERTRAGSID)
    left join V_VERTRAGSORT using (ABSCHLUSSID)
order by PROVISION;

3.4. Abfrage

Führe alle Vorverträge des Vertrages 'Vertrag1Haus' an. (max 10 Punkte)

select lpad('  ', 2 * (LEVEL -1)) || FOLGEVERTRAG as VORVERTRAEGE
from V_VERTRAG
connect by prior FOLGEVERTRAG = VORGAENGERVERTRAG
start with FOLGEVERTRAG = 'Vertrag1Haus';

3.5. Abfrage

Führe die Verträge von Vertretern an, die beim Abschluss mehr als 400 Euro Provision abwarfen.

select NNAME, PLZ, ORT from V_VERTRAG
    join V_VERTRETER using (VERTRETERID)
    join V_VERTRAGSABSCHLUSS using (VERTRAGSID)
where VERTRAGSGROESSE_EURO > 400
  and NOCHGUELTIG = 'JA'
order by 1;