· 7 years ago · Sep 28, 2018, 06:18 AM
1/*
2SQL-script
3Datenbank FoodShop komplett
4zum bearbeiten der Select-Aufgaben
5Ktm, Da, Pm
6*/
7
8#Erstellen DB "FoodShop"
9drop database IF exists FoodShop;
10create database FoodShop;
11
12USE FoodShop;
13
14DROP TABLE IF EXISTS abteilung;
15#
16# Tabellenstruktur fuer Tabelle `abteilung`
17#
18CREATE TABLE abteilung (
19 IDAbteilung int(2) NOT NULL,
20 Bezeichnung varchar(25) NOT NULL default '',
21 PRIMARY KEY (IDAbteilung)
22);
23
24#
25# Daten fuer Tabelle `abteilung`
26#
27INSERT INTO abteilung VALUES(1,'Einkauf');
28INSERT INTO abteilung VALUES(2,'Kueche');
29INSERT INTO abteilung VALUES(3,'Telefondienst');
30INSERT INTO abteilung VALUES(4,'Produktion');
31INSERT INTO abteilung VALUES(5,'Hausmeister');
32# --------------------------------------------------------
33
34DROP TABLE IF EXISTS bestellung;
35#
36# Tabellenstruktur fuer Tabelle `bestellung`
37#
38CREATE TABLE bestellung (
39 IDBestellung int(8) NOT NULL default '0',
40 KundeID int(5) NOT NULL default '0',
41 MitarbeiterID int(3) NOT NULL default '0',
42 Bestelldatum date,
43 Liefdatum date,
44 Bezahlt int(1) NOT NULL default '0',
45 PRIMARY KEY (IDBestellung)
46);
47
48#
49# Daten fuer Tabelle `bestellung`
50#
51INSERT INTO bestellung VALUES(1,1,3,'2001-06-20','2001-06-21',1);
52INSERT INTO bestellung VALUES(2,1,6,'2001-07-22','2001-07-22',1);
53INSERT INTO bestellung VALUES(3,2,3,'2001-07-22','2001-07-23',1);
54INSERT INTO bestellung VALUES(4,3,11,'2001-07-22','2001-07-23',1);
55INSERT INTO bestellung VALUES(5,6,6,'2001-07-22','2001-07-23',0);
56INSERT INTO bestellung VALUES(6,7,9,'2001-07-22','2001-07-23',1);
57INSERT INTO bestellung VALUES(7,16,15,'2001-07-22','2001-07-23',1);
58INSERT INTO bestellung VALUES(8,17,6,'2001-07-22','2001-07-23',1);
59INSERT INTO bestellung VALUES(9,1,3,'2001-07-23','2001-07-24',0);
60INSERT INTO bestellung VALUES(10,3,11,'2001-07-22','2001-07-24',1);
61INSERT INTO bestellung VALUES(11,7,16,'2001-07-23','2001-07-24',1);
62INSERT INTO bestellung VALUES(12,11,6,'2001-07-23','2001-07-24',1);
63INSERT INTO bestellung VALUES(13,14,1,'2001-07-23','2001-07-24',0);
64INSERT INTO bestellung VALUES(14,17,13,'2001-07-23','2001-07-24',1);
65INSERT INTO bestellung VALUES(15,3,1,'2001-07-24','2001-07-25',0);
66INSERT INTO bestellung VALUES(16,4,15,'2001-07-24','2001-07-25',0);
67INSERT INTO bestellung VALUES(17,9,24,'2001-07-24','2001-07-25',1);
68INSERT INTO bestellung VALUES(18,17,24,'2001-07-24','2001-07-25',1);
69INSERT INTO bestellung VALUES(19,1,6,'2001-08-09','2001-08-12',1);
70INSERT INTO bestellung VALUES(20,5,13,'2001-08-09','2001-08-12',1);
71INSERT INTO bestellung VALUES(21,6,17,'2001-08-12','2001-08-13',1);
72INSERT INTO bestellung VALUES(22,11,3,'2001-08-12','2001-08-13',0);
73INSERT INTO bestellung VALUES(23,14,15,'2001-08-21','2001-08-21',1);
74INSERT INTO bestellung VALUES(24,6,2,'2001-08-21','2001-08-22',1);
75INSERT INTO bestellung VALUES(25,12,13,'2001-08-21','2001-08-22',1);
76INSERT INTO bestellung VALUES(26,6,15,'2001-08-21','2001-08-23',1);
77INSERT INTO bestellung VALUES(27,9,1,'2001-08-21','2001-08-23',0);
78INSERT INTO bestellung VALUES(28,7,2,'2001-09-05','2001-09-06',1);
79INSERT INTO bestellung VALUES(29,8,11,'2001-09-09','2001-09-10',1);
80INSERT INTO bestellung VALUES(30,15,16,'2001-09-09','2001-09-10',0);
81INSERT INTO bestellung VALUES(31,8,9,'2001-09-10','2001-09-11',1);
82INSERT INTO bestellung VALUES(32,21,11,'2001-09-10','2001-09-11',1);
83INSERT INTO bestellung VALUES(33,9,1,'2001-09-19','2001-09-23',1);
84INSERT INTO bestellung VALUES(34,13,1,'2001-09-20','2001-09-23',1);
85INSERT INTO bestellung VALUES(35,14,21,'2001-09-20','2001-09-23',0);
86INSERT INTO bestellung VALUES(36,17,9,'2001-09-23','2001-09-26',1);
87INSERT INTO bestellung VALUES(37,18,16,'2001-09-25','2001-09-26',0);
88INSERT INTO bestellung VALUES(38,20,13,'2001-09-23','2001-09-26',1);
89INSERT INTO bestellung VALUES(39,21,6,'2001-09-23','2001-09-26',1);
90INSERT INTO bestellung VALUES(40,10,6,'2001-09-27','2001-09-30',0);
91INSERT INTO bestellung VALUES(41,21,2,'2001-09-25','2001-09-30',1);
92INSERT INTO bestellung VALUES(42,2,15,'2001-10-02','2001-10-03',1);
93INSERT INTO bestellung VALUES(43,3,9,'2001-10-03','2001-10-03',1);
94INSERT INTO bestellung VALUES(44,10,24,'2001-10-02','2001-10-03',1);
95INSERT INTO bestellung VALUES(45,11,24,'2001-10-02','2001-10-03',1);
96INSERT INTO bestellung VALUES(46,12,13,'2001-10-03','2001-10-03',1);
97INSERT INTO bestellung VALUES(47,13,24,'2001-10-02','2001-10-03',1);
98INSERT INTO bestellung VALUES(48,14,2,'2001-10-02','2001-10-03',1);
99INSERT INTO bestellung VALUES(49,5,24,'2001-10-03','2001-10-07',0);
100INSERT INTO bestellung VALUES(50,6,9,'2001-10-03','2001-10-07',1);
101INSERT INTO bestellung VALUES(51,9,24,'2001-10-03','2001-10-07',0);
102INSERT INTO bestellung VALUES(52,13,16,'2001-10-03','2001-10-07',1);
103INSERT INTO bestellung VALUES(53,17,1,'2001-10-03','2001-10-07',1);
104INSERT INTO bestellung VALUES(54,18,17,'2001-10-03','2001-10-07',1);
105INSERT INTO bestellung VALUES(55,1,9,'2001-10-07','2001-10-08',0);
106INSERT INTO bestellung VALUES(56,2,17,'2001-10-07','2001-10-08',0);
107INSERT INTO bestellung VALUES(57,3,21,'2001-10-07','2001-10-08',0);
108INSERT INTO bestellung VALUES(58,4,11,'2001-10-08','2001-10-08',1);
109INSERT INTO bestellung VALUES(59,5,24,'2001-10-07','2001-10-08',0);
110INSERT INTO bestellung VALUES(60,7,15,'2001-10-07','2001-10-08',1);
111INSERT INTO bestellung VALUES(61,9,17,'2001-10-08','2001-10-08',1);
112INSERT INTO bestellung VALUES(62,11,3,'2001-10-07','2001-10-08',0);
113INSERT INTO bestellung VALUES(63,12,15,'2001-10-07','2001-10-08',1);
114INSERT INTO bestellung VALUES(64,15,21,'2001-10-07','2001-10-08',0);
115INSERT INTO bestellung VALUES(65,16,11,'2001-10-07','2001-10-08',0);
116INSERT INTO bestellung VALUES(66,3,24,'2001-10-08','2001-10-09',1);
117INSERT INTO bestellung VALUES(67,5,24,'2001-10-08','2001-10-09',0);
118INSERT INTO bestellung VALUES(68,6,24,'2001-10-09','2001-10-09',0);
119INSERT INTO bestellung VALUES(69,8,3,'2001-10-08','2001-10-09',1);
120INSERT INTO bestellung VALUES(70,9,21,'2001-10-07','2001-10-09',1);
121INSERT INTO bestellung VALUES(71,11,11,'2001-10-08','2001-10-09',1);
122INSERT INTO bestellung VALUES(72,14,11,'2001-10-07','2001-10-09',1);
123INSERT INTO bestellung VALUES(73,18,15,'2001-10-08','2001-10-09',1);
124INSERT INTO bestellung VALUES(74,19,6,'2001-10-07','2001-10-09',0);
125INSERT INTO bestellung VALUES(75,21,21,'2001-10-03','2001-10-09',0);
126INSERT INTO bestellung VALUES(76,2,9,'2001-10-09','2001-10-11',1);
127INSERT INTO bestellung VALUES(77,5,21,'2001-10-07','2001-10-11',1);
128INSERT INTO bestellung VALUES(78,15,1,'2001-10-07','2001-10-11',1);
129INSERT INTO bestellung VALUES(79,17,15,'2001-10-08','2001-10-11',1);
130INSERT INTO bestellung VALUES(80,19,6,'2001-10-11','2001-10-11',1);
131INSERT INTO bestellung VALUES(81,5,2,'2001-10-07','2001-10-16',0);
132INSERT INTO bestellung VALUES(82,6,21,'2001-10-08','2001-10-16',0);
133INSERT INTO bestellung VALUES(83,8,16,'2001-10-11','2001-10-16',1);
134INSERT INTO bestellung VALUES(84,9,6,'2001-10-15','2001-10-16',0);
135INSERT INTO bestellung VALUES(85,1,9,'2001-10-21','2001-10-21',0);
136INSERT INTO bestellung VALUES(86,3,3,'2001-10-15','2001-10-21',1);
137INSERT INTO bestellung VALUES(87,4,11,'2001-10-15','2001-10-21',0);
138INSERT INTO bestellung VALUES(88,5,11,'2001-10-18','2001-10-21',1);
139INSERT INTO bestellung VALUES(89,7,9,'2001-10-18','2001-10-21',1);
140INSERT INTO bestellung VALUES(90,8,24,'2001-10-18','2001-10-21',1);
141INSERT INTO bestellung VALUES(91,17,17,'2001-10-18','2001-10-22',1);
142INSERT INTO bestellung VALUES(92,18,17,'2001-10-18','2001-10-22',0);
143INSERT INTO bestellung VALUES(93,21,11,'2001-10-18','2001-10-22',1);
144INSERT INTO bestellung VALUES(94,3,24,'2001-10-21','2001-10-23',0);
145INSERT INTO bestellung VALUES(95,4,15,'2001-10-18','2001-10-23',1);
146INSERT INTO bestellung VALUES(96,6,6,'2001-10-21','2001-10-23',0);
147INSERT INTO bestellung VALUES(97,7,9,'2001-10-21','2001-10-23',1);
148INSERT INTO bestellung VALUES(98,12,9,'2001-10-18','2001-10-23',1);
149INSERT INTO bestellung VALUES(99,13,1,'2001-10-18','2001-10-23',0);
150INSERT INTO bestellung VALUES(100,11,24,'2001-10-23','2001-10-25',1);
151INSERT INTO bestellung VALUES(101,16,17,'2001-10-23','2001-10-25',0);
152INSERT INTO bestellung VALUES(102,17,13,'2001-10-23','2001-10-25',1);
153INSERT INTO bestellung VALUES(103,19,11,'2001-10-22','2001-10-25',1);
154INSERT INTO bestellung VALUES(104,20,17,'2001-10-24','2001-10-25',1);
155INSERT INTO bestellung VALUES(105,21,6,'2001-10-22','2001-10-25',0);
156INSERT INTO bestellung VALUES(106,2,13,'2001-10-25','2001-10-28',0);
157INSERT INTO bestellung VALUES(107,17,15,'2001-10-25','2001-10-28',1);
158INSERT INTO bestellung VALUES(108,19,3,'2001-10-23','2001-10-28',1);
159INSERT INTO bestellung VALUES(109,20,16,'2001-10-25','2001-10-28',0);
160INSERT INTO bestellung VALUES(110,21,6,'2001-10-25','2001-10-28',0);
161INSERT INTO bestellung VALUES(111,3,2,'2001-10-31','2001-10-31',0);
162INSERT INTO bestellung VALUES(112,4,1,'2001-10-30','2001-10-31',1);
163INSERT INTO bestellung VALUES(113,5,9,'2001-10-31','2001-10-31',1);
164INSERT INTO bestellung VALUES(114,6,21,'2001-10-30','2001-10-31',1);
165INSERT INTO bestellung VALUES(115,8,17,'2001-10-25','2001-10-31',1);
166INSERT INTO bestellung VALUES(116,9,21,'2001-10-25','2001-10-31',0);
167INSERT INTO bestellung VALUES(117,11,1,'2001-10-25','2001-10-31',1);
168INSERT INTO bestellung VALUES(118,20,3,'2001-10-25','2001-10-31',0);
169INSERT INTO bestellung VALUES(119,3,21,'2001-11-04','2001-11-04',1);
170INSERT INTO bestellung VALUES(120,9,17,'2001-10-25','2001-11-04',1);
171INSERT INTO bestellung VALUES(121,12,13,'2001-11-04','2001-11-04',0);
172INSERT INTO bestellung VALUES(122,13,2,'2001-10-31','2001-11-04',1);
173INSERT INTO bestellung VALUES(123,14,11,'2001-10-31','2001-11-04',1);
174INSERT INTO bestellung VALUES(124,16,15,'2001-11-04','2001-11-04',1);
175INSERT INTO bestellung VALUES(125,17,1,'2001-11-04','2001-11-04',1);
176INSERT INTO bestellung VALUES(126,19,2,'2001-10-24','2001-11-04',1);
177INSERT INTO bestellung VALUES(127,21,3,'2001-11-04','2001-11-04',1);
178INSERT INTO bestellung VALUES(128,11,24,'2001-10-24','2001-11-06',1);
179INSERT INTO bestellung VALUES(129,12,13,'2001-11-04','2001-11-06',1);
180INSERT INTO bestellung VALUES(130,13,9,'2001-11-04','2001-11-06',1);
181INSERT INTO bestellung VALUES(131,14,16,'2001-11-04','2001-11-06',1);
182INSERT INTO bestellung VALUES(132,19,11,'2001-11-04','2001-11-06',1);
183INSERT INTO bestellung VALUES(133,1,24,'2001-11-06','2001-11-08',0);
184INSERT INTO bestellung VALUES(134,2,16,'2001-11-04','2001-11-08',0);
185INSERT INTO bestellung VALUES(135,8,11,'2001-11-22','2001-11-25',0);
186INSERT INTO bestellung VALUES(136,14,21,'2001-09-05','2003-09-15',0);
187# --------------------------------------------------------
188
189DROP TABLE IF EXISTS bestpos;
190#
191# Tabellenstruktur fuer Tabelle `bestpos`
192#
193CREATE TABLE bestpos (
194 BestellungID int(8) NOT NULL default '0',
195 ProduktID int(3) NOT NULL default '0',
196 Menge int(4) NOT NULL default '0',
197 PRIMARY KEY (BestellungID, ProduktID)
198);
199
200#
201# Daten fuer Tabelle `bestpos`
202#
203INSERT INTO bestpos VALUES( 1 , 4 , 13 );
204INSERT INTO bestpos VALUES( 2 , 2 , 2 );
205INSERT INTO bestpos VALUES( 3 , 1 , 130 );
206INSERT INTO bestpos VALUES( 3 , 2 , 55 );
207INSERT INTO bestpos VALUES( 3 , 4 , 12 );
208INSERT INTO bestpos VALUES( 4 , 1 , 30 );
209INSERT INTO bestpos VALUES( 4 , 6 , 113 );
210INSERT INTO bestpos VALUES( 5 , 2 , 20 );
211INSERT INTO bestpos VALUES( 6 , 1 , 18 );
212INSERT INTO bestpos VALUES( 7 , 6 , 7 );
213INSERT INTO bestpos VALUES( 8 , 2 , 3 );
214INSERT INTO bestpos VALUES( 9 , 1 , 25 );
215INSERT INTO bestpos VALUES( 10 , 5 , 19 );
216INSERT INTO bestpos VALUES( 11 , 5 , 8 );
217INSERT INTO bestpos VALUES( 12 , 4 , 8 );
218INSERT INTO bestpos VALUES( 13 , 3 , 12 );
219INSERT INTO bestpos VALUES( 14 , 2 , 10 );
220INSERT INTO bestpos VALUES( 15 , 6 , 27 );
221INSERT INTO bestpos VALUES( 16 , 2 , 48 );
222INSERT INTO bestpos VALUES( 16 , 5 , 30 );
223INSERT INTO bestpos VALUES( 17 , 6 , 60 );
224INSERT INTO bestpos VALUES( 18 , 3 , 23 );
225INSERT INTO bestpos VALUES( 19 , 1 , 3 );
226INSERT INTO bestpos VALUES( 20 , 6 , 3 );
227INSERT INTO bestpos VALUES( 20 , 1 , 3 );
228INSERT INTO bestpos VALUES( 21 , 4 , 11 );
229INSERT INTO bestpos VALUES( 21 , 1 , 4 );
230INSERT INTO bestpos VALUES( 22 , 6 , 3 );
231INSERT INTO bestpos VALUES( 23 , 6 , 5 );
232INSERT INTO bestpos VALUES( 24 , 2 , 5 );
233INSERT INTO bestpos VALUES( 24 , 3 , 4 );
234INSERT INTO bestpos VALUES( 25 , 1 , 10 );
235INSERT INTO bestpos VALUES( 26 , 2 , 8 );
236INSERT INTO bestpos VALUES( 27 , 1 , 8 );
237INSERT INTO bestpos VALUES( 28 , 1 , 3 );
238INSERT INTO bestpos VALUES( 29 , 5 , 4 );
239INSERT INTO bestpos VALUES( 30 , 1 , 3 );
240INSERT INTO bestpos VALUES( 31 , 5 , 5 );
241INSERT INTO bestpos VALUES( 32 , 1 , 1 );
242INSERT INTO bestpos VALUES( 33 , 6 , 12 );
243INSERT INTO bestpos VALUES( 33 , 4 , 4 );
244INSERT INTO bestpos VALUES( 34 , 2 , 12 );
245INSERT INTO bestpos VALUES( 35 , 3 , 3 );
246INSERT INTO bestpos VALUES( 36 , 1 , 12 );
247INSERT INTO bestpos VALUES( 37 , 3 , 210 );
248INSERT INTO bestpos VALUES( 38 , 6 , 4 );
249INSERT INTO bestpos VALUES( 39 , 6 , 4 );
250INSERT INTO bestpos VALUES( 40 , 3 , 214 );
251INSERT INTO bestpos VALUES( 40 , 2 , 80 );
252INSERT INTO bestpos VALUES( 41 , 3 , 240 );
253INSERT INTO bestpos VALUES( 42 , 6 , 4 );
254INSERT INTO bestpos VALUES( 43 , 1 , 4 );
255INSERT INTO bestpos VALUES( 44 , 1 , 41 );
256INSERT INTO bestpos VALUES( 45 , 1 , 4 );
257INSERT INTO bestpos VALUES( 46 , 2 , 312 );
258INSERT INTO bestpos VALUES( 47 , 5 , 92 );
259INSERT INTO bestpos VALUES( 48 , 3 , 209 );
260INSERT INTO bestpos VALUES( 49 , 3 , 150 );
261INSERT INTO bestpos VALUES( 50 , 4 , 62 );
262INSERT INTO bestpos VALUES( 51 , 5 , 322 );
263INSERT INTO bestpos VALUES( 52 , 1 , 343 );
264INSERT INTO bestpos VALUES( 53 , 2 , 265 );
265INSERT INTO bestpos VALUES( 54 , 6 , 350 );
266INSERT INTO bestpos VALUES( 55 , 5 , 187 );
267INSERT INTO bestpos VALUES( 55 , 1 , 460 );
268INSERT INTO bestpos VALUES( 56 , 1 , 450 );
269INSERT INTO bestpos VALUES( 57 , 1 , 313 );
270INSERT INTO bestpos VALUES( 58 , 6 , 280 );
271INSERT INTO bestpos VALUES( 59 , 1 , 450 );
272INSERT INTO bestpos VALUES( 60 , 6 , 49 );
273INSERT INTO bestpos VALUES( 61 , 6 , 217 );
274INSERT INTO bestpos VALUES( 62 , 4 , 288 );
275INSERT INTO bestpos VALUES( 63 , 2 , 3 );
276INSERT INTO bestpos VALUES( 64 , 3 , 189 );
277INSERT INTO bestpos VALUES( 64 , 6 , 5 );
278INSERT INTO bestpos VALUES( 65 , 1 , 480 );
279INSERT INTO bestpos VALUES( 66 , 2 , 3 );
280INSERT INTO bestpos VALUES( 67 , 3 , 6 );
281INSERT INTO bestpos VALUES( 67 , 1 , 344 );
282INSERT INTO bestpos VALUES( 68 , 2 , 300 );
283INSERT INTO bestpos VALUES( 69 , 4 , 4 );
284INSERT INTO bestpos VALUES( 70 , 3 , 236 );
285INSERT INTO bestpos VALUES( 70 , 4 , 4 );
286INSERT INTO bestpos VALUES( 71 , 5 , 70 );
287INSERT INTO bestpos VALUES( 71 , 4 , 5 );
288INSERT INTO bestpos VALUES( 72 , 6 , 2 );
289INSERT INTO bestpos VALUES( 73 , 3 , 2 );
290INSERT INTO bestpos VALUES( 74 , 6 , 286 );
291INSERT INTO bestpos VALUES( 75 , 4 , 190 );
292INSERT INTO bestpos VALUES( 76 , 6 , 40 );
293INSERT INTO bestpos VALUES( 77 , 2 , 35 );
294INSERT INTO bestpos VALUES( 78 , 1 , 15 );
295INSERT INTO bestpos VALUES( 79 , 3 , 5 );
296INSERT INTO bestpos VALUES( 80 , 6 , 10 );
297INSERT INTO bestpos VALUES( 81 , 1 , 45 );
298INSERT INTO bestpos VALUES( 82 , 3 , 13 );
299INSERT INTO bestpos VALUES( 83 , 1 , 45 );
300INSERT INTO bestpos VALUES( 84 , 1 , 45 );
301INSERT INTO bestpos VALUES( 85 , 1 , 45 );
302INSERT INTO bestpos VALUES( 86 , 6 , 67 );
303INSERT INTO bestpos VALUES( 87 , 2 , 17 );
304INSERT INTO bestpos VALUES( 88 , 3 , 38 );
305INSERT INTO bestpos VALUES( 89 , 4 , 17 );
306INSERT INTO bestpos VALUES( 90 , 6 , 40 );
307INSERT INTO bestpos VALUES( 91 , 2 , 34 );
308INSERT INTO bestpos VALUES( 92 , 1 , 72 );
309INSERT INTO bestpos VALUES( 93 , 3 , 22 );
310INSERT INTO bestpos VALUES( 93 , 4 , 55 );
311INSERT INTO bestpos VALUES( 93 , 5 , 21 );
312INSERT INTO bestpos VALUES( 94 , 3 , 12 );
313INSERT INTO bestpos VALUES( 95 , 4 , 6 );
314INSERT INTO bestpos VALUES( 96 , 6 , 75 );
315INSERT INTO bestpos VALUES( 97 , 2 , 46 );
316INSERT INTO bestpos VALUES( 98 , 1 , 84 );
317INSERT INTO bestpos VALUES( 99 , 5 , 21 );
318INSERT INTO bestpos VALUES( 100 , 2 , 48 );
319INSERT INTO bestpos VALUES( 101 , 4 , 9 );
320INSERT INTO bestpos VALUES( 102 , 3 , 50 );
321INSERT INTO bestpos VALUES( 103 , 6 , 11 );
322INSERT INTO bestpos VALUES( 104 , 6 , 6 );
323INSERT INTO bestpos VALUES( 104 , 1 , 4 );
324INSERT INTO bestpos VALUES( 105 , 5 , 4 );
325INSERT INTO bestpos VALUES( 106 , 5 , 4 );
326INSERT INTO bestpos VALUES( 107 , 5 , 6 );
327INSERT INTO bestpos VALUES( 108 , 6 , 4 );
328INSERT INTO bestpos VALUES( 109 , 1 , 4 );
329INSERT INTO bestpos VALUES( 110 , 6 , 4 );
330INSERT INTO bestpos VALUES( 111 , 2 , 4 );
331INSERT INTO bestpos VALUES( 112 , 3 , 5 );
332INSERT INTO bestpos VALUES( 113 , 4 , 13 );
333INSERT INTO bestpos VALUES( 114 , 5 , 6 );
334INSERT INTO bestpos VALUES( 115 , 6 , 18 );
335INSERT INTO bestpos VALUES( 116 , 2 , 13 );
336INSERT INTO bestpos VALUES( 117 , 3 , 15 );
337INSERT INTO bestpos VALUES( 117 , 5 , 10 );
338INSERT INTO bestpos VALUES( 118 , 1 , 23 );
339INSERT INTO bestpos VALUES( 119 , 2 , 2 );
340INSERT INTO bestpos VALUES( 120 , 6 , 2 );
341INSERT INTO bestpos VALUES( 121 , 1 , 24 );
342INSERT INTO bestpos VALUES( 122 , 3 , 2 );
343INSERT INTO bestpos VALUES( 122 , 6 , 15 );
344INSERT INTO bestpos VALUES( 123 , 2 , 7 );
345INSERT INTO bestpos VALUES( 124 , 1 , 13 );
346INSERT INTO bestpos VALUES( 125 , 5 , 10 );
347INSERT INTO bestpos VALUES( 126 , 6 , 6 );
348INSERT INTO bestpos VALUES( 127 , 1 , 5 );
349INSERT INTO bestpos VALUES( 127 , 5 , 5 );
350INSERT INTO bestpos VALUES( 128 , 4 , 9 );
351INSERT INTO bestpos VALUES( 129 , 1 , 11 );
352INSERT INTO bestpos VALUES( 130 , 6 , 4 );
353INSERT INTO bestpos VALUES( 131 , 6 , 6 );
354INSERT INTO bestpos VALUES( 132 , 3 , 5 );
355INSERT INTO bestpos VALUES( 133 , 1 , 10 );
356INSERT INTO bestpos VALUES( 134 , 4 , 1 );
357INSERT INTO bestpos VALUES( 135 , 4 , 5 );
358INSERT INTO bestpos VALUES( 135 , 5 , 11 );
359INSERT INTO bestpos VALUES( 135 , 1 , 22 );
360INSERT INTO bestpos VALUES( 136 , 1 , 3 );
361# --------------------------------------------------------
362
363DROP TABLE IF EXISTS funktion;
364#
365# Tabellenstruktur fuer Tabelle `funktion`
366#
367CREATE TABLE funktion (
368 IDFunktion int(2) NOT NULL default '0',
369 Taetigkeit varchar(30) NOT NULL default '',
370 PRIMARY KEY (IDFunktion)
371);
372
373#
374# Daten fuer Tabelle `funktion`
375#
376INSERT INTO funktion VALUES(1, 'Einkaeufer');
377INSERT INTO funktion VALUES(2, 'Fahrer');
378INSERT INTO funktion VALUES(3, 'Gruppenleiter');
379INSERT INTO funktion VALUES(4, 'Hausmeister');
380INSERT INTO funktion VALUES(5, 'Personalreferent');
381INSERT INTO funktion VALUES(6, 'Sachbearbeiter');
382INSERT INTO funktion VALUES(7, 'Fahrer');
383INSERT INTO funktion VALUES(8, 'Koch');
384# --------------------------------------------------------
385
386DROP TABLE IF EXISTS kunde;
387#
388# Tabellenstruktur fuer Tabelle `kunde`
389#
390CREATE TABLE kunde (
391 IDKunde int(5) NOT NULL default '0',
392 Kundenname varchar(30) NOT NULL default '',
393 Strasse varchar(30) NOT NULL default '',
394 Plz varchar(5) NOT NULL default '',
395 Ort varchar(30) NOT NULL default '',
396 Telefon varchar(30) NOT NULL default '',
397 PRIMARY KEY (IDKunde)
398);
399
400#
401# Daten fuer Tabelle `kunde`
402#
403INSERT INTO kunde VALUES(1, 'Fotolabor Farbenfroh', 'Waldstrasse 119', '67444', 'Worms', '(06241)102222');
404INSERT INTO kunde VALUES(2, 'Kaufhaus Teuerkauf', 'Langgasse 20', '65778', 'Wiesbaden', '(0611)566666');
405INSERT INTO kunde VALUES(3, 'Schnulze AG', 'Dorfplatz 50 A', '55431', 'Mainz', '(06131)201030');
406INSERT INTO kunde VALUES(4, 'Chemie-Fabrik OZON', 'Von-Schuhmann-Strasse 120', '55600', 'Mainz', '(06131)333444');
407INSERT INTO kunde VALUES(5, 'Fliesen-Maier', 'Ingelheimer Landstrasse 12', '64529', 'Altenheim', '(06130)655555');
408INSERT INTO kunde VALUES(6, 'Tam-Tam GmbH', 'Hintergasse 12', '63987', 'Wackenhausen', '(05051)1212');
409INSERT INTO kunde VALUES(7, 'Computer Fritz', 'Wilhelmstrasse 40', '65911', 'Wiesbaden', '(0611)454545');
410INSERT INTO kunde VALUES(8, 'Zahnlabor Bohrer', 'Kantheimer Strasse 5', '54181', 'Kantheim', '(03569)723633');
411INSERT INTO kunde VALUES(9, 'Autohaus Kolbenfresser', 'Bahnhofstrasse 30', '55271', 'Eckenreiher', '(04999)98111');
412INSERT INTO kunde VALUES(10, 'Stadtkrankenhaus Wiesbaden', 'Thomas-Mann-Strasse 98', '65300', 'Wiesbaden', '(0611)3598614');
413INSERT INTO kunde VALUES(11, 'Reisebuero Fahrinsland', 'Muehlheimer Weg 3', '52898', 'Landbergheim', '(03111)67890');
414INSERT INTO kunde VALUES(12, 'Blind-Zeitung KG', 'Leo-Kirch-Allee 45', '65292', 'Wiesbaden', '(0611)3516978');
415INSERT INTO kunde VALUES(13, 'Saft-Industrie Dr. Noch', 'Moenchstrasse 23', '56298', 'Haferhofen', '(06780)112222');
416INSERT INTO kunde VALUES(14, 'KLOZ AG', 'Mainzer Strasse 12', '64381', 'Pinsenheim', '(08922)59999');
417INSERT INTO kunde VALUES(15, 'Buecherei Klau', 'Kappelgasse 16', '56711', 'Pappenhausen', '(07891)444111');
418INSERT INTO kunde VALUES(16, 'Gutverpack KG', 'Grosse Bleiche 12', '53002', 'Mainz', '(06131)123456');
419INSERT INTO kunde VALUES(17, 'Kloster Hohenstein', 'Auf dem Berg 1', '53824', 'Semmelhausen', '(05689)666363');
420INSERT INTO kunde VALUES(18, 'Kreditinstitut Sparzins', 'Vordergasse 6', '54181', 'Kantheim', '(03569)987654');
421INSERT INTO kunde VALUES(19, 'Kleinholz & Co.', 'Wasserweg 4', '64529', 'Altenheim', '(06130)756321');
422INSERT INTO kunde VALUES(20, 'BASIS und Partner', 'Hauptstrasse 38', '55271', 'Eckenreiher', '(04999)832147');
423INSERT INTO kunde VALUES(21, 'Porzellan Krach', 'Suedliche Landstrasse 11', '53824', 'Semmelhausen', '(05689)1556789');
424INSERT INTO kunde VALUES(22, 'Buchhandlung Gutenberg', 'Strasse des Buches 4', '83252', 'Kleinsbergen', '(06418)654322');
425# --------------------------------------------------------
426
427DROP TABLE IF EXISTS mitarbeiter;
428#
429# Tabellenstruktur fuer Tabelle `mitarbeiter`
430#
431CREATE TABLE mitarbeiter(
432 IDMitarbeiter int(3) NOT NULL default '0',
433 MitarbeiterName varchar(30) NOT NULL default '',
434 Vorname varchar(30) NOT NULL default '',
435 FunktionID int(2) NOT NULL default '0',
436 AbteilungID int(2) NOT NULL default '0',
437 Gehalt decimal(9,2) NOT NULL default '0.00',
438 eingestellt date NOT NULL default '0000-00-00',
439 PRIMARY KEY (IDMitarbeiter)
440);
441
442#
443# Daten fuer Tabelle `mitarbeiter`
444#
445INSERT INTO mitarbeiter VALUES (1, 'Lorenz', 'Sophia', 1, 1, '1150.00', '1990-01-01');
446INSERT INTO mitarbeiter VALUES (2, 'Ritter', 'Tatjana', 5, 2, '2152.00', '1994-03-01');
447INSERT INTO mitarbeiter VALUES (3, 'Winter', 'Theodor', 6, 3, '1227.00', '1992-04-15');
448INSERT INTO mitarbeiter VALUES (4, 'Richter', 'Hans-Otto', 3, 4, '3150.00', '1994-10-01');
449INSERT INTO mitarbeiter VALUES (5, 'Wieland', 'Brunhilde', 3, 4, '3490.00', '1996-01-01');
450INSERT INTO mitarbeiter VALUES (6, 'Winkler', 'Bettina', 6, 3, '1149.00', '1996-01-01');
451INSERT INTO mitarbeiter VALUES (7, 'Weber', 'Karl-Heinz', 2, 1, '1850.00', '1998-08-08');
452INSERT INTO mitarbeiter VALUES (8, 'Hagen', 'Friedhelm', 5, 2, '3400.00', '1990-07-24');
453INSERT INTO mitarbeiter VALUES (9, 'Schulz', 'Wilfried', 6, 3, '1990.00', '1991-01-01');
454INSERT INTO mitarbeiter VALUES (10, 'Santer', 'Claudia-Maria', 3, 4, '3072.00', '1992-06-20');
455INSERT INTO mitarbeiter VALUES (11, 'Hoelzer', 'Richard', 6, 5, '1445.00', '1996-01-01');
456INSERT INTO mitarbeiter VALUES (12, 'Kamp', 'Klaus-Dieter', 3, 4, '3550.00', '1994-12-01');
457INSERT INTO mitarbeiter VALUES (13, 'Beck', 'Rafaela', 6, 2, '1060.00', '1999-06-15');
458INSERT INTO mitarbeiter VALUES (14, 'Collmar-Schmidt', 'Nadine', 5, 1, '3600.00', '1990-04-15');
459INSERT INTO mitarbeiter VALUES (15, 'Humpe', 'Sybille', 6, 1, '2253.00', '1993-11-01');
460INSERT INTO mitarbeiter VALUES (16, 'Hellmeister', 'Sepp', 6, 4, '1070.00', '1992-09-20');
461INSERT INTO mitarbeiter VALUES (17, 'Soerens', 'Helge', 6, 5, '2155.00', '1991-02-13');
462INSERT INTO mitarbeiter VALUES (18, 'Kaufmann', 'Sonja', 1, 2, '1900.00', '1991-08-01');
463INSERT INTO mitarbeiter VALUES (19, 'Hoffmann', 'Theresa', 3, 2, '3266.00', '1996-02-01');
464INSERT INTO mitarbeiter VALUES (20, 'Klinker', 'Ralf-Ruediger', 2, 3, '1875.00', '1998-02-07');
465INSERT INTO mitarbeiter VALUES (21, 'Baehr', 'Konrad', 6, 4, '2344.00', '1990-08-03');
466INSERT INTO mitarbeiter VALUES (22, 'Gehrke', 'Anna-Marie', 2, 4, '2050.00', '1990-11-20');
467INSERT INTO mitarbeiter VALUES (23, 'Berger', 'Ludwig', 3, 4, '1750.00', '1992-09-01');
468INSERT INTO mitarbeiter VALUES (24, 'Lauterbach', 'Wilma', 6, 3, '2410.00', '1991-05-01');
469INSERT INTO mitarbeiter VALUES (25, 'Koch', 'Heinz-Heribert', 4, 5, '1280.00', '1997-05-01');
470INSERT INTO mitarbeiter VALUES (26, 'Hannes', 'Klaus-Dieter', 4, 1, '1000.00', '1996-08-01');
471INSERT INTO mitarbeiter VALUES (27, 'Kleist', 'Sieglinde', 5, 1, '1775.00', '1999-08-01');
472INSERT INTO mitarbeiter VALUES (28, 'Lindner', 'Dieter', 2, 3, '1425.00', '1996-08-01');
473INSERT INTO mitarbeiter VALUES (29, 'Iwansky', 'Sophia', 5, 4, '1500.00', '1997-07-01');
474INSERT INTO mitarbeiter VALUES (30, 'Wolff', 'Michael', 7, 2, '1110.00', '1997-06-01');
475INSERT INTO mitarbeiter VALUES (31, 'Kaiser', 'Ralf', 6, 2, '1665.00', '1999-09-01');
476# --------------------------------------------------------
477
478DROP TABLE IF EXISTS produkt;
479#
480# Tabellenstruktur fuer Tabelle `produkt`
481#
482CREATE TABLE produkt (
483 IDProdukt int(3) NOT NULL default '0',
484 Bezeichnung varchar(30) NOT NULL default '',
485 Preis decimal(9,2) NOT NULL default '0.00',
486 PRIMARY KEY (IDProdukt)
487);
488
489#
490# Daten fuer Tabelle `produkt`
491#
492INSERT INTO produkt VALUES ('1', 'Vollkost', '8.00');
493INSERT INTO produkt VALUES ('2', 'Salatbar', '6.00');
494INSERT INTO produkt VALUES ('3', 'Schonkost', '6.80');
495INSERT INTO produkt VALUES ('4', 'Vorspeise', '3.00');
496INSERT INTO produkt VALUES ('5', 'Nachspeise', '2.50');
497INSERT INTO produkt VALUES ('6', 'Normalkost', '7.50');
498
499
500#
501# Fremdschlüssel ergänzen
502#
503
504# bestellung (FK) - Kunde (PK)
505ALTER table bestellung
506ADD Foreign key (KundeID)
507References kunde (IDKunde);
508
509# bestellung (FK) - Mitarbeiter (PK)
510ALTER table bestellung
511ADD Foreign key (MitarbeiterID)
512References mitarbeiter (IDMitarbeiter);
513
514ALTER table mitarbeiter
515ADD Foreign key (FunktionID)
516References funktion (IDFunktion);
517
518ALTER table mitarbeiter
519ADD Foreign key (AbteilungID)
520References abteilung (IDAbteilung);
521
522ALTER table bestpos
523ADD Foreign key (BestellungID)
524References bestellung (IDBestellung);
525
526ALTER table bestpos
527ADD Foreign key (ProduktID)
528References produkt (IDProdukt);
529
530# Die Abteilungsbezeichnung „Hausmeister“ ist nicht mehr zeitgemäß und soll in „Facility Management“ umbenannt werden. Die Tabelle abteilung muss geändert werden.
531UPDATE abteilung SET bezeichnung = 'Facility Management' WHERE IDAbteilung = 5;