drop table Verkaeufe;
drop table Kunden;
drop table Filialen;
drop table Verkaeufer;
drop table Produkte;
drop table Zeit;

create table Verkaeufe (
   VerkDatum varchar(30),
   Filiale varchar(20),
   Produkt int,
   Anzahl int,
   Kunde int,
   Verkaeufer int);
create table Kunden (
   KundenNr int,
   Name varchar(30),
   wiealt int);
create table Filialen (
   Filialenkennung varchar(20),
   Land varchar(10),
   Bezirk varchar(20));
create table Verkaeufer(
   VerkaeuferNr int,
   Fachgebiet varchar(20),
   Manager int,
   wiealt int);
create table Produkte(
   ProduktNr int,
   Produkttyp varchar(20),
   Produktgruppe varchar(20),
   Produkthauptgruppe varchar(20),
   Hersteller varchar(20));
create table Zeit(
   Datum varchar(30),
   Tag int,
   Monat char(9),
   Jahr int,
   Quartal int,
   KW integer,
   Wochentag char(8),
   Saison varchar(30));

delete from  Verkaeufe;
delete from  Kunden;
delete from  Filialen;
delete from  Zeit;
delete from  Verkaeufer;
delete from  Produkte;

insert into Verkaeufe values
   ('27-Mar-96','Passau',1347,1,3,825);
insert into Verkaeufe values
   ('27-Apr-96','Passau',1347,1,3,825);
insert into Verkaeufe values
   ('22-Mar-95','Passau',1347,1,3,825);
insert into Verkaeufe values
   ('27-Mar-96','Passau',1348,1,3,825);
insert into Verkaeufe values
   ('27-Apr-96','Passau',1348,1,3,825);
insert into Verkaeufe values
   ('22-Mar-95','Passau',1348,1,3,825);
insert into Verkaeufe values
   ('21-Mar-94','Passau',1348,1,3,825);
insert into Verkaeufe values
   ('27-Mar-96','Passau',1349,1,3,825);
insert into Verkaeufe values
   ('27-Apr-96','Passau',1349,1,3,825);
insert into Verkaeufe values
   ('22-Mar-95','Passau',1349,1,3,825);
insert into Verkaeufe values
   ('21-Mar-94','Passau',1349,1,3,825);
insert into Verkaeufe values
   ('27-Mar-96','Passau',1350,1,3,825);
insert into Verkaeufe values
   ('27-Apr-96','Passau',1350,1,3,825);
insert into Verkaeufe values
   ('22-Mar-95','Passau',1350,1,3,825);
insert into Verkaeufe values
   ('21-Mar-94','Passau',1350,1,3,825);
insert into Verkaeufe values
   ('30-Jul-97','Passau',1350,1,3,825);


insert into Verkaeufe values
   ('27-Mar-96','Linz',1347,1,3,825);
insert into Verkaeufe values
   ('27-Apr-96','Linz',1347,1,3,825);
insert into Verkaeufe values
   ('22-Mar-95','Linz',1347,1,3,825);
insert into Verkaeufe values
   ('21-Mar-94','Linz',1347,1,3,825);
insert into Verkaeufe values
   ('27-Mar-96','Linz',1348,1,3,825);
insert into Verkaeufe values
   ('27-Apr-96','Linz',1348,1,3,825);
insert into Verkaeufe values
   ('22-Mar-95','Linz',1348,1,3,825);
insert into Verkaeufe values
   ('21-Mar-94','Linz',1348,1,3,825);
insert into Verkaeufe values
   ('27-Mar-96','Linz',1349,1,3,825);
insert into Verkaeufe values
   ('27-Apr-96','Linz',1349,1,3,825);
insert into Verkaeufe values
   ('22-Mar-95','Linz',1349,1,3,825);
insert into Verkaeufe values
   ('21-Mar-94','Linz',1349,1,3,825);
insert into Verkaeufe values
   ('27-Mar-96','Linz',1350,1,3,825);
insert into Verkaeufe values
   ('27-Apr-96','Linz',1350,1,3,825);
insert into Verkaeufe values
   ('22-Mar-95','Linz',1350,1,3,825);
insert into Verkaeufe values
   ('21-Mar-94','Linz',1350,1,3,825);

insert into Verkaeufe values
   ('27-Mar-96','Bern',1347,1,3,825);
insert into Verkaeufe values
   ('27-Apr-96','Bern',1347,1,3,825);
insert into Verkaeufe values
   ('22-Mar-95','Bern',1347,1,3,825);
insert into Verkaeufe values
   ('21-Mar-94','Bern',1347,1,3,825);
insert into Verkaeufe values
   ('27-Mar-96','Bern',1348,1,3,825);
insert into Verkaeufe values
   ('27-Apr-96','Bern',1348,1,3,825);
insert into Verkaeufe values
   ('22-Mar-95','Bern',1348,1,3,825);
insert into Verkaeufe values
   ('21-Mar-94','Bern',1348,1,3,825);
insert into Verkaeufe values
   ('27-Mar-96','Bern',1349,1,3,825);
insert into Verkaeufe values
   ('27-Apr-96','Bern',1349,1,3,825);
insert into Verkaeufe values
   ('22-Mar-95','Bern',1349,1,3,825);
insert into Verkaeufe values
   ('21-Mar-94','Bern',1349,1,3,825);
insert into Verkaeufe values
   ('27-Mar-96','Bern',1350,1,3,825);
insert into Verkaeufe values
   ('27-Apr-96','Bern',1350,1,3,825);
insert into Verkaeufe values
   ('22-Mar-95','Bern',1350,1,3,825);
insert into Verkaeufe values
   ('21-Mar-94','Bern',1350,1,3,825);

insert into Filialen values
   ('Passau','D','Bayern');
insert into Filialen values
   ('Linz','Aus','Oberoestereich');
insert into Filialen values
   ('Bern','CH','Oberland');

insert into Kunden values
   (3, 'Kemper', 39);

insert into Kunden values
   (4, 'Gates', 39);

insert into Kunden values
   (2, 'Kohl', 65);

insert into Kunden values
   (1,'Krug',60);

insert into Verkaeufer values
   (825, 'Elektro', 119, 23);

insert into Produkte values
   (1347, 'Handy', 'Mobiltelekom','Telekom', 'Siemens');
insert into Produkte values
   (1348, 'Handy', 'Mobiltelekom','Telekom', 'Nokia');
insert into Produkte values
   (1349, 'Handy', 'Mobiltelekom','Telekom', 'Bosch');
insert into Produkte values
   (1350, 'Handy', 'Mobiltelekom','Telekom', 'Motorola');
insert into Produkte values
   (1351, 'Handy', 'Mobiltelekom','Telekom', 'Ericsson');

insert into Zeit values
   ('30-Jul-97', 30, 'Juli', 1997, 2, 27, 'Montag', 'Hochsommer');
insert into Zeit values
   ('22-Mar-95', 22, 'Maerz', 1995, 1, 12, 'Mittwoch', 'Fruehjahr');
insert into Zeit values
   ('21-Mar-94', 21, 'Maerz', 1994, 1, 12, 'Montag', 'Fruehjahr');
insert into Zeit values
   ('27-Mar-96', 27, 'Maerz', 1996, 1, 13, 'Mittwoch', 'Fruehjahr');
insert into Zeit values
   ('27-Apr-96', 27, 'April', 1996, 1, 17, 'Freitag', 'Fruehjahr');