-
Notifications
You must be signed in to change notification settings - Fork 82
/
Copy pathPOSDB.SQL
371 lines (362 loc) · 19 KB
/
POSDB.SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
BEGIN TRANSACTION;
DROP TABLE IF EXISTS SALE_TRANSACTION;
DROP TABLE IF EXISTS ADDRESS;
DROP TABLE IF EXISTS CATEGORY;
DROP TABLE IF EXISTS EMPLOYEE;
DROP TABLE IF EXISTS INVENTORY_RECORD;
DROP TABLE IF EXISTS JOB_TITLE;
DROP TABLE IF EXISTS PRODUCT;
DROP TABLE IF EXISTS SALE_DETAILS;
CREATE TABLE ADDRESS (
address_id INTEGER NOT NULL PRIMARY KEY,
street_number INTEGER,
suite_number INTEGER,
street_name VARCHAR(45),
street_suffix VARCHAR(45),
city VARCHAR(45),
province VARCHAR(45),
postal_code VARCHAR(45)
);
INSERT INTO ADDRESS VALUES(1,852,NULL,'Fairlawn','Ave','Ottawa','Ontario','K2A4C2');
INSERT INTO ADDRESS VALUES(2,986,'6E','Wonderbar','Lane','Compton','Ohio',226589);
INSERT INTO ADDRESS VALUES(3,839,59,'Main','St','Ottawa','Ontario','K2A4C2');
INSERT INTO ADDRESS VALUES(4,789,'','Slacker','Lane','Toronto','Ontario','K3D4C2');
INSERT INTO ADDRESS VALUES(5,56,8,'Eagleson','Road','Kanata','Ontario','K5FT8S');
CREATE TABLE CATEGORY (
category_id INTEGER NOT NULL PRIMARY KEY,
parent_category_id INTEGER references CATEGORY(category_id),
category_name VARCHAR(45)
);
INSERT INTO CATEGORY VALUES(1,0,'Beverage');
INSERT INTO CATEGORY VALUES(2,0,'Clothing');
INSERT INTO CATEGORY VALUES(3,0,'Food');
INSERT INTO CATEGORY VALUES(4,0,'Electronics');
INSERT INTO CATEGORY VALUES(5,0,'Health Care');
INSERT INTO CATEGORY VALUES(6,1,'Soft drinks');
INSERT INTO CATEGORY VALUES(7,1,'Wine');
INSERT INTO CATEGORY VALUES(8,2,'Shirt');
INSERT INTO CATEGORY VALUES(9,2,'Pants');
INSERT INTO CATEGORY VALUES(10,2,'Footwear');
INSERT INTO CATEGORY VALUES(11,2,'Headgear');
INSERT INTO CATEGORY VALUES(12,2,'Gloves');
INSERT INTO CATEGORY VALUES(13,3,'Groceries');
INSERT INTO CATEGORY VALUES(14,3,'Candy');
INSERT INTO CATEGORY VALUES(15,3,'Baked goods');
INSERT INTO CATEGORY VALUES(16,3,'Hot ready made');
INSERT INTO CATEGORY VALUES(17,3,'Snacks');
INSERT INTO CATEGORY VALUES(18,3,'Meats');
INSERT INTO CATEGORY VALUES(19,4,'Music gear');
INSERT INTO CATEGORY VALUES(20,4,'Entertainment');
INSERT INTO CATEGORY VALUES(21,4,'Game console accesseries');
INSERT INTO CATEGORY VALUES(22,4,'Household electronics');
INSERT INTO CATEGORY VALUES(23,5,'Pharmaceuticals');
INSERT INTO CATEGORY VALUES(24,5,'Family planning');
INSERT INTO CATEGORY VALUES(25,5,'Suppliments');
INSERT INTO CATEGORY VALUES(28,1,'Coffee');
INSERT INTO CATEGORY VALUES(29,0,'General Accessories');
INSERT INTO CATEGORY VALUES(30,29,'Household');
INSERT INTO CATEGORY VALUES(31,29,'Office Supplies');
CREATE TABLE EMPLOYEE (
password TEXT,
employee_number INTEGER NOT NULL PRIMARY KEY,
job_title_id INTEGER,
address_id INTEGER,
fname VARCHAR(45),
lname VARCHAR(45),
date_started TIMESTAMP DEFAULT (datetime('now','localtime')) NOT NULL,
vacation_days_left INTEGER,
hourly_wage INTEGER,
social_insurance INTEGER UNIQUE,
foreign key(job_title_id) references JOB_TITLE(job_title_id),
foreign key(address_id) references ADDRESS(address_id)
);
INSERT INTO EMPLOYEE VALUES('5baa61e4c9b93f3f0682250b6cf8331b7ee68fd8',1,1,1,'Charlie','Li','2015-03-23 00:36:09',100,30,100832579);
INSERT INTO EMPLOYEE VALUES('5baa61e4c9b93f3f0682250b6cf8331b7ee68fd8',2,2,3,'John','Doe','2015-03-23 00:36:09',30,25,123456789);
INSERT INTO EMPLOYEE VALUES('5baa61e4c9b93f3f0682250b6cf8331b7ee68fd8',3,4,4,'Johnny','MacDonald','2015-03-23 00:36:09',30,10,987654321);
INSERT INTO EMPLOYEE VALUES('5baa61e4c9b93f3f0682250b6cf8331b7ee68fd8',4,3,5,'Sally','Robertson','2015-03-23 00:36:09',30,26,456132789);
CREATE TABLE INVENTORY_RECORD(
record_id INT NOT NULL,
product_id INT NOT NULL,
supplier_id INT NULL,
quantity_received INT NULL,
current_quantity INT NULL,
date_received DATETIME NULL,
date_last_modified DATETIME NULL,
incomming_on_order INT NULL,
PRIMARY KEY (record_id, product_id),
FOREIGN KEY (product_id) REFERENCES PRODUCT(product_id)
FOREIGN KEY (supplier_id) REFERENCES SUPPLIER(supplier_id)
);
CREATE TABLE JOB_TITLE (
job_title_id INTEGER NOT NULL PRIMARY KEY,
job_title_name VARCHAR(45)
);
INSERT INTO JOB_TITLE VALUES(1,'Admin');
INSERT INTO JOB_TITLE VALUES(2,'Manager');
INSERT INTO JOB_TITLE VALUES(3,'Accountant');
INSERT INTO JOB_TITLE VALUES(4,'Employee');
CREATE TABLE PRODUCT (
product_id INTEGER NOT NULL PRIMARY KEY,
product_barcode_sku INTEGER,
category_id INTEGER NOT NULL,
product_name VARCHAR(45),
price INTEGER,
date_discontinued DATETIME,
foreign key(category_id) references CATEGORY(category_id)
);
INSERT INTO PRODUCT VALUES(1,100832879,2,'Tank Top',2099,NULL);
INSERT INTO PRODUCT VALUES(2,100879584,7,'Champagne',5500,NULL);
INSERT INTO PRODUCT VALUES(3,100869878,9,'Jeans',3099,NULL);
INSERT INTO PRODUCT VALUES(4,100879887,17,'Potato Chips',399,NULL);
INSERT INTO PRODUCT VALUES(5,100879836,19,'Headphones',4599,NULL);
INSERT INTO PRODUCT VALUES(6,100896345,14,'Sour Patch Kids',199,NULL);
INSERT INTO PRODUCT VALUES(7,100897582,10,'Shoes',4999,NULL);
INSERT INTO PRODUCT VALUES(8,100881469,20,'32'' TV',31999,NULL);
INSERT INTO PRODUCT VALUES(9,100859784,25,'Vitamin D',2499,NULL);
INSERT INTO PRODUCT VALUES(10,100769898,11,'Baseball Cap',1199,NULL);
INSERT INTO PRODUCT VALUES(11,100765898,16,'Soup',199,NULL);
INSERT INTO PRODUCT VALUES(12,100898989,23,'Cough Syrup',899,NULL);
INSERT INTO PRODUCT VALUES(13,100875632,13,'Lettuce',99,NULL);
INSERT INTO PRODUCT VALUES(14,100864987,29,'Bucket',459,NULL);
INSERT INTO PRODUCT VALUES(15,100894686,7,'White Wine',2400,NULL);
INSERT INTO PRODUCT VALUES(16,100698732,7,'Red Wine',3000,NULL);
INSERT INTO PRODUCT VALUES(17,100862318,8,'V-Neck Shirt',1249,NULL);
INSERT INTO PRODUCT VALUES(18,100864879,12,'Leather Gloves',3479,NULL);
INSERT INTO PRODUCT VALUES(19,100995354,11,'Sun Glasses',1769,NULL);
INSERT INTO PRODUCT VALUES(20,1003214589,14,'Chocolate',125,NULL);
INSERT INTO PRODUCT VALUES(21,100965318,14,'Oat Bar',225,NULL);
INSERT INTO PRODUCT VALUES(22,100987555,9,'Khaki',3499,NULL);
INSERT INTO PRODUCT VALUES(23,100799568,9,'Dress Pants',4499,NULL);
INSERT INTO PRODUCT VALUES(24,100987115,9,'Shorts',2699,NULL);
INSERT INTO PRODUCT VALUES(25,100987541,9,'Running Shorts',2299,NULL);
INSERT INTO PRODUCT VALUES(26,100365669,10,'Running Shoes',3999,NULL);
INSERT INTO PRODUCT VALUES(27,100798544,10,'Flip Flop',1999,NULL);
INSERT INTO PRODUCT VALUES(28,100769336,10,'Sandals',2399,NULL);
INSERT INTO PRODUCT VALUES(29,100928653,18,'Steak',2399,NULL);
INSERT INTO PRODUCT VALUES(30,100965866,18,'Salami',759,NULL);
INSERT INTO PRODUCT VALUES(31,100961125,18,'Ham',519,NULL);
INSERT INTO PRODUCT VALUES(32,100698321,18,'Chicken Breast',519,NULL);
INSERT INTO PRODUCT VALUES(33,100695336,17,'Cookie',349,NULL);
INSERT INTO PRODUCT VALUES(34,100961145,16,'French Fries',349,NULL);
INSERT INTO PRODUCT VALUES(35,100965142,16,'Pizza',400,NULL);
INSERT INTO PRODUCT VALUES(36,100994586,31,'Notebooks',200,NULL);
INSERT INTO PRODUCT VALUES(37,100965832,31,'Sticky Notes',225,NULL);
INSERT INTO PRODUCT VALUES(38,100744511,31,'Ball Point Pens',250,NULL);
INSERT INTO PRODUCT VALUES(39,100744596,31,'Pencil',250,NULL);
INSERT INTO PRODUCT VALUES(40,100748699,31,'Binders',250,NULL);
INSERT INTO PRODUCT VALUES(41,100734512,31,'Eraser',125,NULL);
INSERT INTO PRODUCT VALUES(42,100895487,30,'Detergent',2600,NULL);
INSERT INTO PRODUCT VALUES(43,100711997,30,'Duster',1215,NULL);
INSERT INTO PRODUCT VALUES(44,100876312,23,'Flu Medication',1500,NULL);
INSERT INTO PRODUCT VALUES(45,100876967,23,'Cough Drops',600,NULL);
INSERT INTO PRODUCT VALUES(46,100887546,21,'Play Station 4',42599,NULL);
INSERT INTO PRODUCT VALUES(47,100832549,21,'Game Boy Color',12565,NULL);
INSERT INTO PRODUCT VALUES(48,1008665215,21,'Nintendo Wii',26590,NULL);
INSERT INTO PRODUCT VALUES(49,100839165,21,'Xbox Controllers',6099,NULL);
INSERT INTO PRODUCT VALUES(50,100865928,21,'Playstation Controller',5099,NULL);
INSERT INTO PRODUCT VALUES(51,100986224,13,'Apple',99,NULL);
CREATE TABLE SALE_DETAILS (
sale_transaction_id NOT NULL references SALE_TRANSACTION(sale_transaction_id),
product_id NOT NULL references PRODUCT(product_id),
discounted FLOAT,
final_price FLOAT,
quantity_bought VARCHAR(45),
primary key(sale_transaction_id,product_id)
);
INSERT INTO SALE_DETAILS VALUES(1,51,0.0,99.0,2);
INSERT INTO SALE_DETAILS VALUES(1,32,0.0,519.0,1);
INSERT INTO SALE_DETAILS VALUES(2,43,0.0,1215.0,2);
INSERT INTO SALE_DETAILS VALUES(2,41,0.0,125.0,2);
INSERT INTO SALE_DETAILS VALUES(2,33,0.0,349.0,1);
INSERT INTO SALE_DETAILS VALUES(3,12,0.0,899.0,3);
INSERT INTO SALE_DETAILS VALUES(3,45,0.0,600.0,2);
INSERT INTO SALE_DETAILS VALUES(4,2,0.0,5500.0,1);
INSERT INTO SALE_DETAILS VALUES(4,43,0.0,1215.0,1);
INSERT INTO SALE_DETAILS VALUES(4,48,0.0,26590.0,4);
INSERT INTO SALE_DETAILS VALUES(5,32,0.0,519.0,1);
INSERT INTO SALE_DETAILS VALUES(5,20,0.0,125.0,1);
INSERT INTO SALE_DETAILS VALUES(5,43,0.0,1215.0,3);
INSERT INTO SALE_DETAILS VALUES(6,32,0.0,519.0,1);
INSERT INTO SALE_DETAILS VALUES(6,20,0.0,125.0,1);
INSERT INTO SALE_DETAILS VALUES(6,38,0.0,250.0,1);
INSERT INTO SALE_DETAILS VALUES(6,10,0.0,1199.0,1);
INSERT INTO SALE_DETAILS VALUES(7,23,0.0,4499.0,1);
INSERT INTO SALE_DETAILS VALUES(7,41,0.0,125.0,2);
INSERT INTO SALE_DETAILS VALUES(7,33,0.0,349.0,2);
INSERT INTO SALE_DETAILS VALUES(7,3,0.0,3099.0,2);
INSERT INTO SALE_DETAILS VALUES(8,24,0.0,2699.0,1);
INSERT INTO SALE_DETAILS VALUES(8,16,0.0,3000.0,1);
INSERT INTO SALE_DETAILS VALUES(8,5,0.0,4599.0,1);
INSERT INTO SALE_DETAILS VALUES(8,31,0.0,519.0,4);
INSERT INTO SALE_DETAILS VALUES(8,43,0.0,1215.0,1);
INSERT INTO SALE_DETAILS VALUES(9,36,0.0,200.0,2);
INSERT INTO SALE_DETAILS VALUES(9,5,0.0,4599.0,3);
INSERT INTO SALE_DETAILS VALUES(9,45,0.0,600.0,1);
INSERT INTO SALE_DETAILS VALUES(9,9,0.0,2499.0,2);
INSERT INTO SALE_DETAILS VALUES(9,44,0.0,1500.0,3);
INSERT INTO SALE_DETAILS VALUES(10,17,0.0,1249.0,2);
INSERT INTO SALE_DETAILS VALUES(10,1,0.0,2099.0,2);
INSERT INTO SALE_DETAILS VALUES(10,49,0.0,6099.0,2);
INSERT INTO SALE_DETAILS VALUES(10,37,0.0,225.0,1);
INSERT INTO SALE_DETAILS VALUES(10,19,0.0,1769.0,2);
INSERT INTO SALE_DETAILS VALUES(10,29,0.0,2399.0,2);
INSERT INTO SALE_DETAILS VALUES(10,15,0.0,2400.0,2);
INSERT INTO SALE_DETAILS VALUES(10,9,0.0,2499.0,2);
INSERT INTO SALE_DETAILS VALUES(11,29,0.0,2399.0,4);
INSERT INTO SALE_DETAILS VALUES(11,6,0.0,199.0,3);
INSERT INTO SALE_DETAILS VALUES(12,27,0.0,1999.0,2);
INSERT INTO SALE_DETAILS VALUES(12,25,0.0,2299.0,4);
INSERT INTO SALE_DETAILS VALUES(13,25,0.0,2299.0,2);
INSERT INTO SALE_DETAILS VALUES(13,24,0.0,2699.0,2);
INSERT INTO SALE_DETAILS VALUES(13,23,0.0,4499.0,3);
INSERT INTO SALE_DETAILS VALUES(14,17,0.0,1249.0,6);
INSERT INTO SALE_DETAILS VALUES(15,39,0.0,250.0,2);
INSERT INTO SALE_DETAILS VALUES(15,21,0.0,225.0,2);
INSERT INTO SALE_DETAILS VALUES(15,22,0.0,3499.0,4);
INSERT INTO SALE_DETAILS VALUES(15,34,0.0,349.0,4);
INSERT INTO SALE_DETAILS VALUES(16,13,0.0,99.0,1);
INSERT INTO SALE_DETAILS VALUES(16,35,0.0,400.0,4);
INSERT INTO SALE_DETAILS VALUES(16,25,0.0,2299.0,5);
INSERT INTO SALE_DETAILS VALUES(17,51,0.0,99.0,1);
INSERT INTO SALE_DETAILS VALUES(17,32,0.0,519.0,2);
INSERT INTO SALE_DETAILS VALUES(17,23,0.0,4499.0,3);
INSERT INTO SALE_DETAILS VALUES(18,5,0.0,4599.0,2);
INSERT INTO SALE_DETAILS VALUES(18,36,0.0,200.0,3);
INSERT INTO SALE_DETAILS VALUES(18,31,0.0,519.0,2);
INSERT INTO SALE_DETAILS VALUES(18,48,0.0,26590.0,2);
INSERT INTO SALE_DETAILS VALUES(19,26,0.0,3999.0,2);
INSERT INTO SALE_DETAILS VALUES(19,39,0.0,250.0,2);
INSERT INTO SALE_DETAILS VALUES(19,11,0.0,199.0,2);
INSERT INTO SALE_DETAILS VALUES(19,9,0.0,2499.0,2);
INSERT INTO SALE_DETAILS VALUES(20,12,0.0,899.0,4);
INSERT INTO SALE_DETAILS VALUES(21,8,0.0,31999.0,3);
INSERT INTO SALE_DETAILS VALUES(21,2,0.0,5500.0,2);
INSERT INTO SALE_DETAILS VALUES(21,42,0.0,2600.0,2);
INSERT INTO SALE_DETAILS VALUES(22,5,0.0,4599.0,2);
INSERT INTO SALE_DETAILS VALUES(22,22,0.0,3499.0,2);
INSERT INTO SALE_DETAILS VALUES(22,44,0.0,1500.0,2);
INSERT INTO SALE_DETAILS VALUES(22,12,0.0,899.0,5);
INSERT INTO SALE_DETAILS VALUES(22,14,0.0,459.0,3);
INSERT INTO SALE_DETAILS VALUES(22,24,0.0,2699.0,3);
INSERT INTO SALE_DETAILS VALUES(23,25,0.0,2299.0,3);
INSERT INTO SALE_DETAILS VALUES(23,30,0.0,759.0,4);
INSERT INTO SALE_DETAILS VALUES(23,28,0.0,2399.0,2);
INSERT INTO SALE_DETAILS VALUES(23,7,0.0,4999.0,2);
INSERT INTO SALE_DETAILS VALUES(24,49,0.0,6099.0,4);
INSERT INTO SALE_DETAILS VALUES(24,15,0.0,2400.0,2);
INSERT INTO SALE_DETAILS VALUES(24,6,0.0,199.0,2);
INSERT INTO SALE_DETAILS VALUES(25,23,0.0,4499.0,2);
INSERT INTO SALE_DETAILS VALUES(25,51,0.0,99.0,3);
INSERT INTO SALE_DETAILS VALUES(25,8,0.0,31999.0,4);
INSERT INTO SALE_DETAILS VALUES(26,3,0.0,3099.0,1);
INSERT INTO SALE_DETAILS VALUES(26,27,0.0,1999.0,2);
INSERT INTO SALE_DETAILS VALUES(26,41,0.0,125.0,3);
INSERT INTO SALE_DETAILS VALUES(26,48,0.0,26590.0,1);
INSERT INTO SALE_DETAILS VALUES(26,23,0.0,4499.0,3);
INSERT INTO SALE_DETAILS VALUES(26,47,0.0,12565.0,1);
INSERT INTO SALE_DETAILS VALUES(26,11,0.0,199.0,2);
INSERT INTO SALE_DETAILS VALUES(27,51,0.0,99.0,2);
INSERT INTO SALE_DETAILS VALUES(27,32,0.0,519.0,2);
INSERT INTO SALE_DETAILS VALUES(27,21,0.0,225.0,2);
INSERT INTO SALE_DETAILS VALUES(27,35,0.0,400.0,2);
INSERT INTO SALE_DETAILS VALUES(27,33,0.0,349.0,2);
INSERT INTO SALE_DETAILS VALUES(27,34,0.0,349.0,1);
INSERT INTO SALE_DETAILS VALUES(27,30,0.0,759.0,2);
INSERT INTO SALE_DETAILS VALUES(27,4,0.0,399.0,2);
INSERT INTO SALE_DETAILS VALUES(28,21,0.0,225.0,2);
INSERT INTO SALE_DETAILS VALUES(28,6,0.0,199.0,3);
INSERT INTO SALE_DETAILS VALUES(28,20,0.0,125.0,4);
INSERT INTO SALE_DETAILS VALUES(29,7,0.0,4999.0,1);
INSERT INTO SALE_DETAILS VALUES(29,28,0.0,2399.0,5);
INSERT INTO SALE_DETAILS VALUES(30,2,0.0,5500.0,1);
INSERT INTO SALE_DETAILS VALUES(30,42,0.0,2600.0,2);
INSERT INTO SALE_DETAILS VALUES(30,23,0.0,4499.0,3);
INSERT INTO SALE_DETAILS VALUES(30,47,0.0,12565.0,2);
INSERT INTO SALE_DETAILS VALUES(31,40,0.0,250.0,7);
INSERT INTO SALE_DETAILS VALUES(31,10,0.0,1199.0,3);
INSERT INTO SALE_DETAILS VALUES(32,27,0.0,1999.0,1);
INSERT INTO SALE_DETAILS VALUES(32,44,0.0,1500.0,3);
INSERT INTO SALE_DETAILS VALUES(32,12,0.0,899.0,3);
INSERT INTO SALE_DETAILS VALUES(33,14,0.0,459.0,3);
INSERT INTO SALE_DETAILS VALUES(33,37,0.0,225.0,2);
INSERT INTO SALE_DETAILS VALUES(33,40,0.0,250.0,1);
INSERT INTO SALE_DETAILS VALUES(33,38,0.0,250.0,1);
INSERT INTO SALE_DETAILS VALUES(33,36,0.0,200.0,1);
INSERT INTO SALE_DETAILS VALUES(34,33,0.0,349.0,1);
INSERT INTO SALE_DETAILS VALUES(34,8,0.0,31999.0,7);
INSERT INTO SALE_DETAILS VALUES(34,20,0.0,125.0,2);
INSERT INTO SALE_DETAILS VALUES(34,10,0.0,1199.0,2);
INSERT INTO SALE_DETAILS VALUES(34,41,0.0,125.0,2);
INSERT INTO SALE_DETAILS VALUES(34,36,0.0,200.0,1);
INSERT INTO SALE_DETAILS VALUES(34,27,0.0,1999.0,1);
INSERT INTO SALE_DETAILS VALUES(34,40,0.0,250.0,2);
INSERT INTO SALE_DETAILS VALUES(34,51,0.0,99.0,1);
INSERT INTO SALE_DETAILS VALUES(34,48,0.0,26590.0,2);
INSERT INTO SALE_DETAILS VALUES(34,4,0.0,399.0,1);
INSERT INTO SALE_DETAILS VALUES(34,50,0.0,5099.0,3);
INSERT INTO SALE_DETAILS VALUES(34,46,0.0,42599.0,5);
INSERT INTO SALE_DETAILS VALUES(34,32,0.0,519.0,1);
INSERT INTO SALE_DETAILS VALUES(35,2,0.0,5500.0,1);
INSERT INTO SALE_DETAILS VALUES(35,16,0.0,3000.0,2);
INSERT INTO SALE_DETAILS VALUES(36,2,0.0,5500.0,4);
INSERT INTO SALE_DETAILS VALUES(37,15,0.0,2400.0,4);
INSERT INTO SALE_DETAILS VALUES(38,16,0.0,3000.0,3);
INSERT INTO SALE_DETAILS VALUES(39,2,0.0,5500.0,9);
INSERT INTO SALE_DETAILS VALUES(39,16,0.0,3000.0,8);
INSERT INTO SALE_DETAILS VALUES(39,15,0.0,2400.0,9);
INSERT INTO SALE_DETAILS VALUES(40,2,0.0,5500.0,11);
INSERT INTO SALE_DETAILS VALUES(40,16,0.0,3000.0,10);
INSERT INTO SALE_DETAILS VALUES(41,8,0.0,31999.0,6);
INSERT INTO SALE_DETAILS VALUES(42,13,0.0,99.0,3);
INSERT INTO SALE_DETAILS VALUES(42,47,0.0,12565.0,4);
CREATE TABLE SALE_TRANSACTION (
sale_transaction_id INTEGER NOT NULL PRIMARY KEY,
employee_number INTEGER NOT NULL,
item_total VARCHAR(45),
tax INTEGER, total_sum INTEGER,
datetime_sold TIMESTAMP DEFAULT (datetime('now','localtime')) NOT NULL,
foreign key(employee_number) references EMPLOYEE(employee_number)
);
INSERT INTO SALE_TRANSACTION VALUES(1,1,3,93,810,'2015-04-03 20:59:51');
INSERT INTO SALE_TRANSACTION VALUES(2,1,5,394,3423.0,'2015-04-03 20:59:57');
INSERT INTO SALE_TRANSACTION VALUES(3,1,5,507,4404,'2015-04-03 21:00:07');
INSERT INTO SALE_TRANSACTION VALUES(4,1,6,14700,127775,'2015-04-05 07:00:46');
INSERT INTO SALE_TRANSACTION VALUES(5,1,5,558,4847,'2015-04-05 17:22:34');
INSERT INTO SALE_TRANSACTION VALUES(6,1,4,272,2365,'2015-04-05 21:41:22');
INSERT INTO SALE_TRANSACTION VALUES(7,1,7,1514,13159,'2015-04-05 21:52:34');
INSERT INTO SALE_TRANSACTION VALUES(8,1,8,1767.0,15356,'2015-04-05 23:48:23');
INSERT INTO SALE_TRANSACTION VALUES(9,1,11,3158,27453.0,'2015-04-05 23:48:33');
INSERT INTO SALE_TRANSACTION VALUES(10,1,15,4843,42096,'2015-04-05 23:48:51');
INSERT INTO SALE_TRANSACTION VALUES(11,1,7,1325,11518,'2015-04-05 23:48:57');
INSERT INTO SALE_TRANSACTION VALUES(12,1,6,1715.0,14909,'2015-04-05 23:49:05');
INSERT INTO SALE_TRANSACTION VALUES(13,1,7,3054,26547.0,'2015-04-05 23:49:15');
INSERT INTO SALE_TRANSACTION VALUES(14,1,6,974,8468,'2015-04-05 23:49:24');
INSERT INTO SALE_TRANSACTION VALUES(15,1,12,2124,18466,'2015-04-05 23:49:37');
INSERT INTO SALE_TRANSACTION VALUES(16,1,10,1715.0,14909,'2015-04-05 23:50:09');
INSERT INTO SALE_TRANSACTION VALUES(17,3,6,1902,16536,'2015-04-05 23:59:16');
INSERT INTO SALE_TRANSACTION VALUES(18,3,9,8322,72338,'2015-04-05 23:59:23');
INSERT INTO SALE_TRANSACTION VALUES(19,3,8,1806.0,15700,'2015-04-05 23:59:31');
INSERT INTO SALE_TRANSACTION VALUES(20,3,4,467,4063.0,'2015-04-05 23:59:39');
INSERT INTO SALE_TRANSACTION VALUES(21,3,7,14586.0,126783,'2015-04-05 23:59:56');
INSERT INTO SALE_TRANSACTION VALUES(22,3,17,4311,37476,'2015-03-29 00:00:16');
INSERT INTO SALE_TRANSACTION VALUES(23,3,11,3215,27944,'2015-03-29 00:00:24');
INSERT INTO SALE_TRANSACTION VALUES(24,3,8,3847,33441,'2015-03-29 00:00:32');
INSERT INTO SALE_TRANSACTION VALUES(25,2,9,17848,155139,'2015-03-29 00:00:48');
INSERT INTO SALE_TRANSACTION VALUES(26,2,13,7868.0,68390,'2015-03-29 00:05:20');
INSERT INTO SALE_TRANSACTION VALUES(27,2,15,760,6609,'2015-03-29 00:05:31');
INSERT INTO SALE_TRANSACTION VALUES(28,2,9,201.0,1748,'2015-03-29 00:05:41');
INSERT INTO SALE_TRANSACTION VALUES(29,2,6,2209,19203,'2015-03-29 00:08:38');
INSERT INTO SALE_TRANSACTION VALUES(30,2,8,6413,55740,'2015-03-29 00:12:06');
INSERT INTO SALE_TRANSACTION VALUES(31,2,10,695,6042,'2015-03-29 00:12:14');
INSERT INTO SALE_TRANSACTION VALUES(32,2,7,1195,10391,'2015-03-29 00:14:15');
INSERT INTO SALE_TRANSACTION VALUES(33,2,8,329,2856,'2015-03-29 00:14:27');
INSERT INTO SALE_TRANSACTION VALUES(34,2,31,66616,579044,'2015-03-29 00:17:52');
INSERT INTO SALE_TRANSACTION VALUES(35,2,3,1495,12995.0,'2015-03-29 00:18:53');
INSERT INTO SALE_TRANSACTION VALUES(36,2,4,2860,24860,'2015-03-29 00:18:58');
INSERT INTO SALE_TRANSACTION VALUES(37,2,4,1248,10848,'2015-03-29 00:19:03');
INSERT INTO SALE_TRANSACTION VALUES(38,2,3,1170,10170,'2015-03-29 00:19:09');
INSERT INTO SALE_TRANSACTION VALUES(39,2,26,12363,107463.0,'2015-03-29 00:19:22');
INSERT INTO SALE_TRANSACTION VALUES(40,2,21,11765,102265,'2015-03-29 00:19:29');
INSERT INTO SALE_TRANSACTION VALUES(41,2,6,24959,216953.0,'2015-03-29 00:20:22');
INSERT INTO SALE_TRANSACTION VALUES(42,2,7,6572,57129,'2015-03-29 00:22:10');
COMMIT;