alter session set nls_date_format='DD.MM.YYYY';

drop table meiste_vertraege;
drop table sonderpraemie;
drop table region;
drop table vertreter;

create table vertreter
	(vertreternr decimal(4) primary key,
	name varchar(15),
	kurzzeichen varchar(3) unique,
	geburtsjahr decimal(4),
	geschlecht char(1) check (geschlecht in ('M','W')),
	angestellt_seit decimal(4),
	strasse varchar(20),
	hausnummer varchar(4),
	postleitzahl varchar(6),
	stadt varchar(10),
	telefonnummer varchar(10),
	bundesland varchar(20)); -- fk zu region???

create table region
	(region varchar(20) primary key,
	regionsleiter decimal(4) references vertreter,
	versicherungssparte varchar(20));

create table sonderpraemie
	(praemiennummer decimal(4) primary key,
	vertreternr decimal(4) references vertreter,
	praemien_datum date,
	praemien_betrag decimal(7,2));

create table meiste_vertraege
	(quartalnr decimal(5),
	region varchar(20) references region,
	primary key (quartalnr,region),
	vertreternr decimal(4) references vertreter,
	anzahl_abgeschl_vertraege decimal(4),
	anzahl_verlorene_vertraege decimal(4));

delete from vertreter;

insert into vertreter values
(6,'Huber','HUB',1964,'M',1977,'Schillerstrasse','80','4040','Linz','476537','OOE');
insert into vertreter values
(44,'Maier','MAI',1963,'M',1980,'Koernerstrasse','23','4444','Wels','368753','OOE');
insert into vertreter values
(83,'Hofer','HOF',1956,'M',2002,'Magdalenastrasse','16A','4231','Narndorf','353548','OOE');
insert into vertreter values
(2,'Engelberger','ENG',1948,'M',1999,'Steinweg','43','3432','Salzburg','237893','Salzburg');
insert into vertreter values
(27,'Kottan','KOT',1964,'W',1999,'Leidenweg','80','4534','Perg','234857','OOE');
insert into vertreter values
(104,'Mueller','MUE',1970,'W',1984,'Suedstrasse','65','4534','Perg','987571','OOE');
insert into vertreter values
(7,'Weber','WEB',1963,'M',1981,'Eisweg','39','9758','Oberberg','347689','Salzburg');
insert into vertreter values
(57,'Berger','BEA',1971,'M',1985,'Oberweg','16','4377','Gmunden','473458','OOE');
insert into vertreter values
(39,'Bauer','BAU',1956,'M',1980,'Hauptplatz','78','9629','Obertauern','393435','Salzburg');
insert into vertreter values
(112,'Berger','BEB',1963,'W',1984,'Freistrasse','8','6392','Radstatt','548745','Salzburg');
insert into vertreter values
(8,'Neumueller','NEU',1962,'W',1980,'Bahnhofstrasse','4','6584','Freistadt','458458','OOE');
insert into vertreter values
(100,'Pratt','PRA',1963,'M',1979,'Hintergasse','80','1234','Stratford','494593','OOE');
insert into vertreter values
(28,'Kaibl','KAI',1963,'W',1979,'Old Main Road','10','1299','Linz','659599','OOE');
insert into vertreter values
(95,'Morgen','MOR',1963,'M',1972,'Hauptstrasse','33A','5746','Wels','867564','OOE');

select * from vertreter order by vertreternr;

delete from region;

insert into region values ('OOE',6,'KFZ-Versicherung');
insert into region values ('Salzburg',27,'Lebensversicherung');

select * from region;

delete from sonderpraemie;

insert into sonderpraemie values (1,6,'08.12.1999',1000.0);
insert into sonderpraemie values (2,44,'05.05.2002',750.0);
insert into sonderpraemie values (3,27,'10.09.2008',1000.0);
insert into sonderpraemie values (4,104,'08.12.2002',500.0);
insert into sonderpraemie values (5,44,'08.12.2007',250.0);
insert into sonderpraemie values (6,8,'08.12.2005',250.0);
insert into sonderpraemie values (7,44,'30.12.2007',300.0);
insert into sonderpraemie values (8,27,'12.11.2006',750.0);

select * from sonderpraemie;

delete from meiste_vertraege;

insert into meiste_vertraege values (20041,'OOE',6,3,1);
insert into meiste_vertraege values (20042,'OOE',6,2,3);
insert into meiste_vertraege values (20043,'OOE',6,3,0);
insert into meiste_vertraege values (20044,'OOE',44,3,2);
insert into meiste_vertraege values (20051,'OOE',83,0,3);
insert into meiste_vertraege values (20052,'OOE',2,1,3);
insert into meiste_vertraege values (20053,'OOE',57,3,0);
insert into meiste_vertraege values (20054,'OOE',8,0,3);
insert into meiste_vertraege values (20061,'Salzburg',27,3,2);
insert into meiste_vertraege values (20062,'Salzburg',104,3,2);
insert into meiste_vertraege values (20063,'Salzburg',112,2,3);
insert into meiste_vertraege values (20064,'Salzburg',112,1,3);
insert into meiste_vertraege values (20071,'Salzburg',8,0,3);

select * from meiste_vertraege;

commit;
