Beispieldatenbank (Datenbankname: beispiel1)


create table kunde
(
kund_nr char(6),
kund_name char(30),
adresse char(30),
ort char(20),
plz int
);

insert into kunde values 
( 'GK4441', 'Mueller & Partner', 'Schillerstr. 56', 'Stuttgart', 74598 );
insert into kunde values 
( 'GK4442', 'Stadtverwaltung Landshut', 'Am  Rathausplatz', 'Landshut', 85321 );
insert into kunde values 
( 'GK4477', 'Betten Kaiser ', 'Industriepark', 'Goettingen', 35134 );

insert into kunde values 
( 'GK4489', 'Stadtverwaltung Köln ', 'Am Dom 8', 'Koeln', 50987 );
insert into kunde values 
( 'EK5689', 'Paula Peterson ', 'Maistr. 46', 'Muenchen', 84977 ); 
insert into kunde values 
( 'EK5558', 'Manfred Keller GmbH ', 'Mozartstr. 45', 'Stuttgart', 74391 );

insert into kunde values 
( 'GK4333', 'Planungsbuero Bertoldt ', 'Bauhofstr. 87', 'Duisburg', 49155 );
insert into kunde values 
( 'GK4688', 'Ulrich & Co.', 'Rosenheimer Str. 234', 'Muenchen',  81377 ); 
insert into kunde values 
( 'EK8992', 'Peter Hauser ', 'Am Bismarkturm 2', 'Stuttgart', 76232); 

insert into kunde values 
( 'EK8993', 'Miriam Zechmeister KG ', 'Gänsemarkt 36', 'Hamburg', 21357); 
insert into kunde values 
( 'GK4443', 'Freiling & Partner', 'Engerser Landstr. 17', 'Koblenz', 54621); 
insert into kunde values 
( 'GK4490', 'Einrichtungshaus Roeder ', 'Rheinlanddamm 199', 'Bonn', 50321); 

insert into kunde values 
( 'GK4491', 'Stuhl Kaiser ', 'Im Tal 4', 'Muenchen', 80432); 
insert into kunde values 
( 'GK4492', 'Planmöbel GmbH ', 'Am Stockborn 5', 'Frankfurt', 61234); 
insert into kunde values 
( 'GK4493', 'Systemmöbel Wisst & Co. ', 'Greinstr. 2', 'Koeln', 53261);


create table personal
(
persnr int,
nachname char(20),
vorname char(15),
einsatz char(15),
vorgesetzt int,
gehalt int
);

insert into personal values (1, 'Winter', 'Margot', 'MUENCHEN', 8, 4200 ); 
insert into personal values (2, 'Roser', 'Michael', 'HAMBURG', 0, 5200 ); 
insert into personal values (3, 'Thomas', 'Fred', 'HAMBURG', 2, 4000 ); 
insert into personal values (4, 'Scholl', 'Friedrich', 'MUENCHEN', 8, 5300 ); 
insert into personal values (5, 'Hartinger', 'Roswita', 'MUENCHEN', 8, 4800 ); 
insert into personal values (6, 'Reiztig', 'Hans-Peter', 'FRANKFURT', 7, 5550 ); 

insert into personal values (7, 'Moll', 'Rolf', 'FRANKFURT', 0, 5900 ); 
insert into personal values (8, 'Sandner', 'Ernst', 'MUENCHEN', 0, 6300 ); 
insert into personal values (9, 'Starck', 'Brigitte', 'KOELN', 0, 5300 ); 
insert into personal values (10, 'Anger', 'Lars', 'KOELN', 9, 5200 ); 
insert into personal values (11, 'Freudenfeld', 'Burghard', 'MUENCHEN', 8, 5500 ); 
insert into personal values (12, 'Neumann', 'Gabriele', 'HAMBURG', 2, 4900 ); 

create table verkauf
(
auftr_nr int,
bestelldat char(8),
persnr int,
kund_nr char(6)
);

insert into verkauf values (20002, '23.07.88', 5, 'GK4688' );
insert into verkauf values (20003, '23.07.88', 1, 'EK8992' );
insert into verkauf values (20004, '23.07.88', 8, 'EK5558' );
insert into verkauf values (20005, '24.07.88', 7, 'GK4491' );
insert into verkauf values (20006, '24.07.88', 12, 'EK8993' );
insert into verkauf values (20007, '24.07.88', 12, 'GK4477' );
insert into verkauf values (20008, '24.07.88', 1, 'GK4442' );
insert into verkauf values (20009, '24.07.88', 9, 'GK4492' );
insert into verkauf values (20010, '24.07.88', 11, 'EK5689' );
insert into verkauf values (20011, '24.07.88', 8, 'GK4441' );


create table ausgang
(
auftr_nr int,
art_nr int,
menge int
);

insert into ausgang values (20002, 104002, 2 );
insert into ausgang values (20002, 203333, 3 );
insert into ausgang values (20002, 201080, 1 );
insert into ausgang values (20003, 104888, 5 );
insert into ausgang values (20003, 301001, 1 );
insert into ausgang values (20004, 104003, 1 );
insert into ausgang values (20004, 201081, 2 );
insert into ausgang values (20004, 203333, 3 );

insert into ausgang values (20005, 104789, 1 );
insert into ausgang values (20006, 104003, 1 );
insert into ausgang values (20006, 201080, 2 );
insert into ausgang values (20007, 401000, 2 );
insert into ausgang values (20007, 401001, 2 );
insert into ausgang values (20008, 401002, 1 );

insert into ausgang values (20008, 401000, 1 );
insert into ausgang values (20008, 203333, 2 );
insert into ausgang values (20009, 301001, 1 );
insert into ausgang values (20010, 301001, 4 );
insert into ausgang values (20011, 301001, 1 );
insert into ausgang values (20011, 401000, 1 );
insert into ausgang values (20011, 401001, 1 );


create table inventar
(
art_nr int,
art_bez char(30),
lagerbest int,
lagerort char(20),
preis float
);

insert into inventar values (104002, 'BUECHERREGAL', 2, 'MUENCHEN', 4100.00 ); 
insert into inventar values (104002, 'BUECHERREGAL', 3, 'FRANKFURT', 4100.00 ); 
insert into inventar values (104002, 'BUECHERREGAL', 1, 'KOELN', 4100.00 ); 
insert into inventar values (301001, 'SCHLAFZIMMER', 6, 'MUENCHEN', 4200.00 ); 
insert into inventar values (301001, 'SCHLAFZIMMER', 20, 'KOELN', 4200.00 ); 
insert into inventar values (104789, 'BAUERNSCHRANK', 5, 'FRANKFURT', 780.00 ); 
insert into inventar values (201080, 'COUCH STOCKHOLM', 1, 'FRANKFURT',4800.00 ); 

insert into inventar values (201080, 'COUCH STOCKHOLM', 4, 'MUENCHEN', 4800.00 ); 
insert into inventar values (201080, 'COUCH STOCKHOLM', 2, 'HAMBURG', 4800.00 ); 
insert into inventar values (201081, 'COUCH MIRABELL', 2, 'MUENCHEN', 2700.00 ); 
insert into inventar values (203333, 'COUCH GARNITUR KLASSIKA', 6, 'KOELN', 4300.00 ); 
insert into inventar values (203333, 'COUCH GARNITUR KLASSIKA', 10, 'HAMBURG', 4300.00 ); 
insert into inventar values (203333, 'COUCH GARNITUR KLASSIKA', 1, 'MUENCHEN', 4300.00 );
 
insert into inventar values (203333, 'COUCH GARNITUR KLASSIKA', 4, 'FRANKFURT', 4300.00 ); 
insert into inventar values (201299, 'COUCH GARNITUR LUXORETT', 2, 'FRANKFURT', 10400.00 ); 
insert into inventar values (104888, 'KLEIDERSCHRANK', 5, 'MUENCHEN', 680.00 ); 
insert into inventar values (104888, 'KLEIDERSCHRANK', 12, 'FRANKFURT', 680.00 ); 
insert into inventar values (104888, 'KLEIDERSCHRANK', 8, 'HAMBURG', 680.00 ); 
insert into inventar values (401000, 'SCHREIBTISCH FUTURA', 4, 'MUENCHEN', 4200.00 );
insert into inventar values (401000, 'SCHREIBTISCH FUTURA', 5, 'HAMBURG', 4200.00 ); 
 
insert into inventar values (401001, 'DREHSTUHL FUTURA', 4, 'MUENCHEN', 1800.00 ); 
insert into inventar values (401001, 'DREHSTUHL FUTURA', 5, 'HAMBURG', 1800.00 ); 
insert into inventar values (401002, 'BUEROSYSTEM FUTURA T2', 3, 'MUENCHEN', 7500.00 ); 
insert into inventar values (401002, 'BUEROSYSTEM FUTURA T2', 5, 'HAMBURG', 7500.00 );
insert into inventar values (104003, 'SCHRANKWAND', 0, 'MUENCHEN', 10500.00 ); 
insert into inventar values (104003, 'SCHRANKWAND', 2, 'HAMBURG', 10500.00 );