Лаврентев Освоение СQЛ 2009
.pdf81
INSERT INTO sales_order VALUES( |
567 |
, '05-JUL-1991' , |
202 , '07-JUL-1991' , 200 ); |
||||||
INSERT INTO sales_order VALUES( |
570 |
, '17-JUL-1991' , |
202 , '24-JUL-1991' , 663.6 ); |
||||||
INSERT INTO sales_order VALUES( |
571 |
, '02-AUG-1991' , |
202 , '12-AUG-1991' , 1095.6 ); |
||||||
INSERT INTO sales_order VALUES( |
541 |
, '08-AUG-1990' , |
203 , '18-AUG-1990' , 400 ); |
||||||
INSERT INTO sales_order VALUES( |
532 |
, '08-APR-1990' , |
203 , '21-APR-1990' , 1295.4 ); |
||||||
INSERT INTO sales_order VALUES( |
527 |
, '27-FEB-1990' , |
204 , '14-MAR-1990' , 3054.4 ); |
||||||
INSERT INTO sales_order VALUES( |
501 |
, '06-JAN-1989' , |
204 , '11-JAN-1989' , 216 ); |
||||||
INSERT INTO sales_order VALUES( |
564 |
, '25-MAY-1991' , |
204 , '26-MAY-1991' , 898.8 ); |
||||||
INSERT INTO sales_order VALUES( |
537 |
, '14-JUN-1990' , |
204 , '26-JUN-1990' , 672.9 ); |
||||||
INSERT INTO sales_order VALUES( |
522 |
, '07-JAN-1990' , |
204 , '10-JAN-1990' , 2578.8 ); |
||||||
INSERT INTO sales_order VALUES( |
554 |
, '18-FEB-1991' , |
204 , '22-FEB-1991' , 282.8 ); |
||||||
INSERT INTO sales_order VALUES( |
569 |
, '16-JUL-1991' , |
205 , '23-JUL-1991' , 2073 ); |
||||||
INSERT INTO sales_order VALUES( |
550 |
, '27-JAN-1991' , |
205 , '07-FEB-1991' , 2356 ); |
||||||
INSERT INTO sales_order VALUES( |
548 |
, '26-NOV-1990' , |
205 , '12-DEC-1990' , 666.9 ); |
||||||
INSERT INTO sales_order VALUES( |
505 |
, '07-APR-1989' , |
206 , '22-APR-1989' , 650 ); |
||||||
INSERT INTO sales_order VALUES( |
577 |
, '06-OCT-1991' , |
206 , '20-OCT-1991' , 1265.75 ); |
||||||
INSERT INTO sales_order VALUES( |
533 |
, '28-APR-1990' , |
206 , '02-MAY-1990' , 1122.1 ); |
||||||
INSERT INTO sales_order VALUES( |
561 |
, '20-APR-1991' , |
207 , '07-MAY-1991' , 2558.3 ); |
||||||
INSERT INTO sales_order VALUES( |
506 |
, '27-APR-1989' , |
208 , '10-MAY-1989' , 2600.4 ); |
||||||
INSERT INTO sales_order VALUES( |
530 |
, '03-APR-1990' , |
208 , '07-APR-1990' , 3026.5 ); |
||||||
INSERT INTO sales_order VALUES( |
545 |
, '06-OCT-1990' , |
208 , '13-OCT-1990' , 475 ); |
||||||
INSERT INTO sales_order VALUES( |
557 |
, '08-MAR-1991' , |
208 , '12-MAR-1991' , 2461.8 ); |
||||||
INSERT INTO sales_order VALUES( |
546 |
, '17-OCT-1990' , |
208 , '23-OCT-1990' , 3663 ); |
||||||
INSERT INTO sales_order VALUES( |
510 |
, '18-JUL-1989' , |
208 , '21-JUL-1989' , |
1336.6 ); |
|||||
CREATE TABLE product( |
|
|
|
|
|
|
|
|
|
product_id NUMBER(6,0) NOT NULL, |
|
|
|
|
|
|
|
|
|
description VARCHAR2(30) NULL, |
|
|
|
|
|
|
|
|
|
CHECK (product_id IS NOT NULL), |
|
|
|
|
|
|
|
|
|
PRIMARY KEY (product_id) |
|
|
|
|
|
|
|
|
|
); |
|
|
|
|
|
|
|
|
|
INSERT INTO product VALUES( |
100860 |
, 'ACE TENNIS RACKET I' |
|
); |
|
|
|||
INSERT INTO product VALUES( |
100861 |
, 'ACE TENNIS RACKET II' |
|
); |
|
|
|||
INSERT INTO product VALUES( |
100870 |
, 'ACE TENNIS BALLS-3 PACK' |
); |
|
|
||||
INSERT INTO product VALUES( |
100871 |
, 'ACE TENNIS BALLS-6 PACK' |
); |
|
|
||||
INSERT INTO product VALUES( |
100890 |
, 'ACE TENNIS NET' |
); |
|
|
|
|
||
INSERT INTO product VALUES( |
101860 |
, 'SP TENNIS RACKET' |
); |
|
|
|
|||
INSERT INTO product VALUES( |
101863 |
, 'SP JUNIOR RACKET' |
); |
|
|
|
|||
INSERT INTO product VALUES( |
102130 |
, 'RH: "GUIDE TO TENNIS"' |
|
); |
|
|
|||
INSERT INTO product VALUES( |
200376 |
, 'SB ENERGY BAR-6 PACK' |
|
); |
|
|
|||
INSERT INTO product VALUES( |
200380 |
, 'SB VITA SNACK-6 PACK' |
|
); |
|
|
|||
INSERT INTO product VALUES( |
103120 |
, 'WIFF SOFTBALL BAT I' |
|
); |
|
|
|||
INSERT INTO product VALUES( |
103121 |
, 'WIFF SOFTBALL BAT II' |
|
); |
|
|
|||
INSERT INTO product VALUES( |
103130 |
, 'WIFF SOFTBALL, SMALL' |
|
); |
|
|
|||
INSERT INTO product VALUES( |
103131 |
, 'WIFF SOFTBALL, LARGE' |
|
); |
|
|
|||
INSERT INTO product VALUES( |
103140 |
, 'WIFF SOFTBALL MITT (LH)' |
|
); |
|
|
|||
INSERT INTO product VALUES( |
103141 |
, 'WIFF SOFTBALL MITT (RH)' |
|
); |
|
|
|||
INSERT INTO product VALUES( |
102132 |
, 'RH: "GUIDE TO SOFTBALL"' |
|
); |
|
|
|||
INSERT INTO product VALUES( |
104350 |
, 'DUNK BASKETBALL INDOOR' |
); |
|
|||||
INSERT INTO product VALUES( |
104351 |
, 'DUNK BASKETBALL OUTDOOR' |
); |
|
|||||
INSERT INTO product VALUES( |
104352 |
, 'DUNK BASKETBALL PROFESSIONAL' |
); |
||||||
INSERT INTO product VALUES( |
104360 |
, 'DUNK HOOP' |
); |
|
|
|
|
||
INSERT INTO product VALUES( |
104361 |
, 'DUNK HOOP W/FIBERGLASS BOARD' |
); |
||||||
INSERT INTO product VALUES( |
104362 |
, 'DUNK NETS - RAINBOW' |
|
); |
|
|
|||
INSERT INTO product VALUES( |
102134 |
, 'RH: "GUIDE TO BASKETBALL"' ); |
|
||||||
INSERT INTO product VALUES( |
105123 |
, 'YELLOW JERSEY BICYCLE HELMET' |
); |
||||||
INSERT INTO product VALUES( |
105124 |
, 'YELLOW JERSEY BICYCLE GLOVES' |
); |
||||||
INSERT INTO product VALUES( |
105125 |
, 'YELLOW JERSEY WATER BOTTLE' |
); |
||||||
INSERT INTO product VALUES( |
105126 |
, 'YELLOW JERSEY BOTTLE CAGE' |
); |
|
|||||
82
INSERT INTO product VALUES( |
105127 |
, 'YELLOW JERSEY FRAME PUMP' |
); |
|
INSERT INTO product VALUES( |
105128 |
, 'YELLOW JERSEY SADDLE PACK' |
); |
|
INSERT INTO product VALUES( |
102136 |
, 'RH: "GUIDE TO CYCLING"' |
); |
|
CREATE TABLE item(
order_id NUMBER(4,0) NOT NULL, item_id NUMBER(4,0) NOT NULL, product_id NUMBER(6,0) NULL, actual_price NUMBER(8,2) NULL, quantity NUMBER(8,0) NULL,
total NUMBER(8,2) NULL, CHECK (order_id IS NOT NULL), CHECK (item_id IS NOT NULL), PRIMARY KEY (order_id, item_id),
FOREIGN KEY (order_id) REFERENCES sales_order(order_id), FOREIGN KEY (product_id) REFERENCES product(product_id) );
INSERT INTO item VALUES( |
600 , |
1 , |
100861 , |
42 |
, |
1 , |
|
42 ); |
|
INSERT INTO item VALUES( |
610 , |
3 , |
100890 , |
58 |
, |
1 , |
|
58 ); |
|
INSERT INTO item VALUES( |
611 , |
1 , |
100861 , |
45 |
, |
1 , |
|
45 ); |
|
INSERT INTO item VALUES( |
612 , |
1 , |
100860 , |
30 |
, |
100 |
, |
3000 ); |
|
INSERT INTO item VALUES( |
601 , |
1 , |
200376 , |
2.4 |
, |
12 |
, |
|
28.8 ); |
INSERT INTO item VALUES( |
601 , |
2 , |
100860 , |
32 |
, |
1 , |
|
32 ); |
|
INSERT INTO item VALUES( |
602 , |
1 , |
100870 , |
2.8 |
, |
20 |
, |
|
56 ); |
INSERT INTO item VALUES( |
604 , |
1 , |
100890 , |
58 |
, |
3 , |
|
174 ); |
|
INSERT INTO item VALUES( |
604 , |
2 , |
100861 , |
42 |
, |
2 , |
|
84 ); |
|
INSERT INTO item VALUES( |
604 , |
3 , |
100860 , |
32 |
, |
12 |
, |
|
384 ); |
INSERT INTO item VALUES( |
603 , |
1 , |
100860 , |
32 |
, |
7 , |
|
224 ); |
|
INSERT INTO item VALUES( |
610 , |
1 , |
100860 , |
35 |
, |
1 , |
|
35 ); |
|
INSERT INTO item VALUES( |
610 , |
2 , |
100870 , |
2.8 |
, |
3 , |
|
8.4 ); |
|
INSERT INTO item VALUES( |
613 , |
4 , |
200376 , |
2.2 |
, |
200 |
, |
440 ); |
|
INSERT INTO item VALUES( |
614 , |
1 , |
100860 , |
35 |
, |
444 |
, |
15540 ); |
|
INSERT INTO item VALUES( |
614 , |
2 , |
100870 , |
2.8 |
, |
1000 , |
2800 ); |
||
INSERT INTO item VALUES( |
612 , |
2 , |
100861 , |
40.5 , |
20 |
, |
810 ); |
||
INSERT INTO item VALUES( |
612 , |
3 , |
101863 , |
10 |
, |
150 |
, |
1500 ); |
|
INSERT INTO item VALUES( |
620 , |
1 , |
100860 , |
35 |
, |
10 |
, |
|
350 ); |
INSERT INTO item VALUES( |
620 , |
2 , |
200376 , |
2.4 , |
1000 , |
2400 ); |
|||
INSERT INTO item VALUES( |
620 , |
3 , |
102130 , |
3.4 |
, |
500 |
, |
1700 ); |
|
INSERT INTO item VALUES( |
613 , |
1 , |
100871 , |
5.6 |
, |
100 |
, |
560 ); |
|
INSERT INTO item VALUES( |
613 , |
2 , |
101860 , |
24 |
, |
200 |
, |
4800 ); |
|
INSERT INTO item VALUES( |
613 , |
3 , |
200380 , |
4 , |
|
150 |
, |
|
600 ); |
INSERT INTO item VALUES( |
619 , |
3 , |
102130 , |
3.4 |
, |
100 , |
340 ); |
||
INSERT INTO item VALUES( |
617 , |
1 , |
100860 , |
35 |
, |
50 |
, |
|
1750 ); |
INSERT INTO item VALUES( |
617 , |
2 , |
100861 , |
45 |
, |
100 |
, |
4500 ); |
|
INSERT INTO item VALUES( |
614 , |
3 , |
100871 , |
5.6 |
, |
1000 , |
5600 ); |
||
INSERT INTO item VALUES( |
616 , |
1 , |
100861 , |
45 |
, |
10 |
, |
450 ); |
|
INSERT INTO item VALUES( |
616 , |
2 , |
100870 , |
2.8 |
, |
50 |
, |
|
140 ); |
INSERT INTO item VALUES( |
616 , |
3 , |
100890 , |
58 |
, |
2 , |
|
116 ); |
|
INSERT INTO item VALUES( |
616 , |
4 , |
102130 , |
3.4 |
, |
10 |
, |
|
34 ); |
INSERT INTO item VALUES( |
616 , |
5 , |
200376 , |
2.4 |
, |
10 |
, |
|
24 ); |
INSERT INTO item VALUES( |
619 , |
1 , |
200380 , |
4 , |
100 , |
400 ); |
|||
INSERT INTO item VALUES( |
619 , |
2 , |
200376 , |
2.4 |
, |
100 |
, |
240 ); |
|
INSERT INTO item VALUES( |
615 , |
1 , |
100861 , |
45 |
, |
4 , |
|
180 ); |
|
INSERT INTO item VALUES( |
607 , |
1 , |
100871 , |
5.6 |
, |
1 , |
|
5.6 ); |
|
INSERT INTO item VALUES( |
615 , |
2 , |
100870 , |
2.8 |
, |
100 |
, |
280 ); |
|
INSERT INTO item VALUES( |
617 , |
3 , |
100870 , |
2.8 |
, |
500 |
, |
1400 ); |
|
INSERT INTO item VALUES( |
617 , |
4 , |
100871 , |
5.6 |
, |
500 |
, |
2800 ); |
|
INSERT INTO item VALUES( |
617 , |
5 , |
100890 , |
58 |
, |
500 |
, |
29000 ); |
|
INSERT INTO item VALUES( |
617 , |
6 , |
101860 , |
24 |
, |
100 |
, |
2400 ); |
|
INSERT INTO item VALUES( |
617 , |
7 , |
101863 , |
12.5 , |
200 , |
2500 ); |
|||
83
INSERT INTO item VALUES( |
617 , |
8 , |
102130 , |
3.4 |
, |
100 |
, |
340 ); |
||
INSERT INTO item VALUES( |
617 , |
9 , |
200376 , |
2.4 |
, |
200 |
, |
480 ); |
||
INSERT INTO item VALUES( |
617 , |
10 , |
200380 , |
4 , |
300 , |
1200 ); |
||||
INSERT INTO item VALUES( |
609 , |
2 , |
100870 , |
2.5 |
, |
5 |
, |
|
12.5 ); |
|
INSERT INTO item VALUES( |
609 , |
3 , |
100890 , |
50 |
, |
1 |
, |
|
50 ); |
|
INSERT INTO item VALUES( |
618 , |
1 , |
100860 , |
35 |
, |
23 , |
805 ); |
|||
INSERT INTO item VALUES( |
618 , |
2 , |
100861 , |
45 |
, |
50 |
, |
|
2250 ); |
|
INSERT INTO item VALUES( |
618 , |
3 , |
100870 , |
2.8 |
, |
10 |
, |
|
28 ); |
|
INSERT INTO item VALUES( |
621 , |
1 , |
100861 , |
45 |
, |
10 |
, |
|
450 ); |
|
INSERT INTO item VALUES( |
621 , |
2 , |
100870 , |
2.8 |
, |
100 |
, |
280 ); |
||
INSERT INTO item VALUES( |
615 , |
3 , |
100871 , |
5 , |
|
50 |
, |
|
250 ); |
|
INSERT INTO item VALUES( |
608 , |
1 , |
101860 , |
24 |
, |
1 |
, |
|
24 ); |
|
INSERT INTO item VALUES( |
608 , |
2 , |
100871 , |
5.6 |
, |
2 |
, |
|
11.2 ); |
|
INSERT INTO item VALUES( |
609 , |
1 , |
100861 , |
40 |
, |
1 |
, |
|
40 ); |
|
INSERT INTO item VALUES( |
606 , |
1 , |
102130 , |
3.4 , |
1 , |
|
3.4 ); |
|||
INSERT INTO item VALUES( |
605 , |
1 , |
100861 , |
45 |
, |
100 |
, |
4500 ); |
||
INSERT INTO item VALUES( |
605 , |
2 , |
100870 , |
2.8 |
, |
500 |
, |
1400 ); |
||
INSERT INTO item VALUES( |
605 , |
3 , |
100890 , |
58 |
, |
5 |
, |
|
290 ); |
|
INSERT INTO item VALUES( |
605 , |
4 , |
101860 , |
24 |
, |
50 |
, |
|
1200 ); |
|
INSERT INTO item VALUES( |
605 , |
5 , |
101863 , |
9.5 |
, |
100 , |
950 ); |
|||
INSERT INTO item VALUES( |
605 , |
6 , |
102130 , |
3.4 |
, |
10 |
, |
|
34 ); |
|
INSERT INTO item VALUES( |
612 , |
4 , |
100871 , |
5.5 |
, |
100 , |
550 ); |
|||
INSERT INTO item VALUES( |
619 , |
4 , |
100871 , |
5.6 |
, |
50 |
, |
|
280 ); |
|
INSERT INTO item VALUES( |
509 , |
1 , |
105123 , |
35 |
, |
30 |
, |
|
1050 ); |
|
INSERT INTO item VALUES( |
509 , |
2 , |
105124 , |
9 , |
|
10 |
, |
|
90 ); |
|
INSERT INTO item VALUES( |
509 , |
3 , |
102136 , |
3.4 |
, |
10 |
, |
34 ); |
||
INSERT INTO item VALUES( |
523 , |
1 , |
102136 , |
3.4 |
, |
10 |
, |
|
34 ); |
|
INSERT INTO item VALUES( |
523 , |
2 , |
105123 , |
37.7 , |
30 |
, |
1131 ); |
|||
INSERT INTO item VALUES( |
549 , |
1 , |
105123 , |
38 |
, |
5 |
, |
|
190 ); |
|
INSERT INTO item VALUES( |
549 , |
2 , |
105127 , |
6 , |
|
30 |
, |
|
180 ); |
|
INSERT INTO item VALUES( |
549 , |
3 , |
105125 , |
3 , |
|
200 |
, |
|
600 ); |
|
INSERT INTO item VALUES( |
549 , |
4 , |
105124 , |
13 |
, |
50 , |
650 ); |
|||
INSERT INTO item VALUES( |
507 , |
1 , |
105123 , |
35.3 , |
20 |
, |
706 ); |
|||
INSERT INTO item VALUES( |
507 , |
2 , |
105124 , |
9 , |
|
20 |
, |
|
180 ); |
|
INSERT INTO item VALUES( |
516 , |
1 , |
105124 , |
9 , |
|
60 |
, |
|
540 ); |
|
INSERT INTO item VALUES( |
516 , |
2 , |
102136 , |
3.2 |
, |
250 |
, |
800 ); |
||
INSERT INTO item VALUES( |
516 , |
3 , |
105125 , |
1.9 |
, |
250 |
, |
475 ); |
||
INSERT INTO item VALUES( |
553 , |
1 , |
105127 , |
6 , |
|
500 |
, |
|
3000 ); |
|
INSERT INTO item VALUES( |
553 , |
2 , |
200376 , |
1.75 , |
800 , |
1400 ); |
||||
INSERT INTO item VALUES( |
526 , |
1 , |
105123 , |
32 |
, |
100 |
, |
3200 ); |
||
INSERT INTO item VALUES( |
526 , |
2 , |
105124 , |
9 , |
500 , |
4500 ); |
||||
INSERT INTO item VALUES( |
543 , |
1 , |
105123 , |
34 |
, |
100 |
, |
3400 ); |
||
INSERT INTO item VALUES( |
543 , |
2 , |
105124 , |
10 |
, |
500 |
, |
5000 ); |
||
INSERT INTO item VALUES( |
555 , |
1 , |
105123 , |
34 |
, |
100 , |
3400 ); |
|||
INSERT INTO item VALUES( |
555 , |
2 , |
105124 , |
10 |
, |
500 |
, |
5000 ); |
||
INSERT INTO item VALUES( |
555 , |
3 , |
102136 , |
2.8 |
, |
50 |
, |
|
140 ); |
|
INSERT INTO item VALUES( |
559 , |
1 , |
105123 , |
40 |
, |
5 |
, |
|
200 ); |
|
INSERT INTO item VALUES( |
559 , |
2 , |
105124 , |
15 |
, |
5 |
, |
|
75 ); |
|
INSERT INTO item VALUES( |
559 , |
3 , |
105127 , |
6 , |
|
5 , |
|
|
30 ); |
|
INSERT INTO item VALUES( |
559 , |
4 , |
102136 , |
3.4 |
, |
3 , |
|
10.2 ); |
||
INSERT INTO item VALUES( |
559 , |
5 , |
200376 , |
2.4 |
, |
30 |
, |
|
72 ); |
|
INSERT INTO item VALUES( |
528 , |
1 , |
105123 , |
37.7 , |
100 , |
3770 ); |
||||
INSERT INTO item VALUES( |
531 , |
1 , |
105124 , |
11 |
, |
100 |
, |
1100 ); |
||
INSERT INTO item VALUES( |
531 , |
2 , |
102136 , |
3 , |
100 |
, |
300 ); |
|||
INSERT INTO item VALUES( |
558 , |
1 , |
105124 , |
14 |
, |
100 |
, |
1400 ); |
||
INSERT INTO item VALUES( |
558 , |
2 , |
102136 , |
3 , |
|
100 |
, |
|
300 ); |
|
INSERT INTO item VALUES( |
525 , |
1 , |
105123 , |
37.7 , |
10 , |
377 ); |
||||
INSERT INTO item VALUES( |
552 , |
1 , |
105123 , |
40 |
, |
10 |
, |
|
400 ); |
|
INSERT INTO item VALUES( |
552 , |
2 , |
105124 , |
15 |
, |
10 |
, |
|
150 ); |
|
INSERT INTO item VALUES( |
552 , |
3 , |
102136 , |
3.4 |
, |
1 |
, |
|
3.4 ); |
|
INSERT INTO item VALUES( |
552 , |
4 , |
200376 , |
2.4 |
, |
1 |
, |
|
2.4 ); |
|
INSERT INTO item VALUES( |
556 , |
1 , |
102136 , |
3.4 |
, |
25 |
, |
|
85 ); |
|
84
INSERT INTO item VALUES( |
560 , |
1 , |
200376 , |
2.4 |
, |
|
30 , |
|
72 ); |
|
INSERT INTO item VALUES( |
565 , |
1 , |
105123 , |
37 |
, |
|
100 |
, |
|
3700 ); |
INSERT INTO item VALUES( |
565 , |
2 , |
105124 , |
12 |
, |
|
100 |
, |
|
1200 ); |
INSERT INTO item VALUES( |
574 , |
1 , |
104350 , |
41.8 , |
25 |
, |
1045 ); |
|||
INSERT INTO item VALUES( |
574 , |
2 , |
200380 , |
3.2 |
, |
|
200 |
, |
|
640 ); |
INSERT INTO item VALUES( |
576 , |
1 , |
104350 , |
44 |
, |
|
10 , |
|
440 ); |
|
INSERT INTO item VALUES( |
576 , |
2 , |
104351 , |
26 |
, |
|
10 , |
|
260 ); |
|
INSERT INTO item VALUES( |
576 , |
3 , |
104362 , |
4.25 , |
|
170 |
, |
722.5 ); |
||
INSERT INTO item VALUES( |
576 , |
4 , |
200376 , |
2.16 , |
|
90 |
, |
|
194.4 ); |
|
INSERT INTO item VALUES( |
576 , |
5 , |
200380 , |
3.4 |
, |
|
130 |
, |
|
442 ); |
INSERT INTO item VALUES( |
503 , |
1 , |
104350 , |
38 |
, |
|
30 , |
|
1140 ); |
|
INSERT INTO item VALUES( |
503 , |
2 , |
104351 , |
23.6 , |
|
10 |
, |
|
236 ); |
|
INSERT INTO item VALUES( |
503 , |
3 , |
104352 , |
50 |
, |
|
10 , |
|
500 ); |
|
INSERT INTO item VALUES( |
518 , |
1 , |
104350 , |
38 |
, |
|
25 , |
|
950 ); |
|
INSERT INTO item VALUES( |
518 , |
2 , |
104351 , |
23.6 , |
|
20 |
, |
|
472 ); |
|
INSERT INTO item VALUES( |
518 , |
3 , |
104360 , |
36 , |
|
20 , |
|
720 ); |
||
INSERT INTO item VALUES( |
518 , |
4 , |
104362 , |
4.25 , |
|
170 |
, |
722.5 ); |
||
INSERT INTO item VALUES( |
518 , |
5 , |
102134 , |
3.4 |
, |
|
20 , |
|
68 ); |
|
INSERT INTO item VALUES( |
517 , |
1 , |
104352 , |
50 |
, |
|
15 , |
|
750 ); |
|
INSERT INTO item VALUES( |
517 , |
2 , |
102134 , |
3.4 |
, |
|
10 , |
|
34 ); |
|
INSERT INTO item VALUES( |
544 , |
1 , |
104350 , |
44 |
, |
|
15 , |
|
660 ); |
|
INSERT INTO item VALUES( |
544 , |
2 , |
104351 , |
26 |
, |
|
15 , |
|
390 ); |
|
INSERT INTO item VALUES( |
544 , |
3 , |
104352 , |
58.3 , |
|
10 |
, |
|
583 ); |
|
INSERT INTO item VALUES( |
544 , |
4 , |
104361 , |
50 |
, |
|
10 , |
|
500 ); |
|
INSERT INTO item VALUES( |
544 , |
5 , |
104362 , |
4.5 |
, |
|
50 , |
|
225 ); |
|
INSERT INTO item VALUES( |
524 , |
1 , |
104350 , |
42 |
, |
|
10 , |
|
420 ); |
|
INSERT INTO item VALUES( |
524 , |
2 , |
104351 , |
23.56 |
, |
35 |
, |
824.6 ); |
||
INSERT INTO item VALUES( |
524 , |
3 , |
104362 , |
4.25 , |
|
150 |
, |
637.5 ); |
||
INSERT INTO item VALUES( |
524 , |
4 , |
102134 , |
3.23 , |
|
30 |
, |
|
96.9 ); |
|
INSERT INTO item VALUES( |
502 , |
1 , |
104352 , |
50 , |
|
10 , |
|
500 ); |
||
INSERT INTO item VALUES( |
539 , |
1 , |
104351 , |
26 |
, |
|
20 , |
|
520 ); |
|
INSERT INTO item VALUES( |
539 , |
2 , |
104360 , |
39 |
, |
|
20 , |
|
780 ); |
|
INSERT INTO item VALUES( |
511 , |
1 , |
104351 , |
23.6 , |
|
15 , |
354 ); |
|||
INSERT INTO item VALUES( |
511 , |
2 , |
104362 , |
4.5 |
, |
|
50 , |
|
225 ); |
|
INSERT INTO item VALUES( |
511 , |
3 , |
102134 , |
3.4 |
, |
|
20 , |
|
68 ); |
|
INSERT INTO item VALUES( |
512 , |
1 , |
104360 , |
36 |
, |
|
10 , |
|
360 ); |
|
INSERT INTO item VALUES( |
512 , |
2 , |
102134 , |
3.4 |
, |
|
20 , |
|
68 ); |
|
INSERT INTO item VALUES( |
562 , |
1 , |
104351 , |
24.7 , |
|
25 |
, |
|
617.5 ); |
|
INSERT INTO item VALUES( |
562 , |
2 , |
104352 , |
58.3 , |
|
15 |
, |
|
874.5 ); |
|
INSERT INTO item VALUES( |
562 , |
3 , |
104362 , |
4.25 , |
|
130 |
, |
552.5 ); |
||
INSERT INTO item VALUES( |
529 , |
1 , |
104350 , |
39.89 |
, |
30 |
, |
1196.7 ); |
||
INSERT INTO item VALUES( |
529 , |
2 , |
102134 , |
3.4 , |
|
20 , |
|
68 ); |
||
INSERT INTO item VALUES( |
563 , |
1 , |
104352 , |
58.3 , |
|
10 |
, |
|
583 ); |
|
INSERT INTO item VALUES( |
563 , |
2 , |
200376 , |
2.04 , |
|
150 |
, |
306 ); |
||
INSERT INTO item VALUES( |
568 , |
1 , |
104351 , |
24.7 , |
|
30 , |
741 ); |
|||
INSERT INTO item VALUES( |
568 , |
2 , |
200376 , |
2.04 , |
|
110 |
, |
224.4 ); |
||
INSERT INTO item VALUES( |
568 , |
3 , |
200380 , |
3.6 |
, |
|
70 , |
|
252 ); |
|
INSERT INTO item VALUES( |
504 , |
1 , |
104350 , |
40 |
, |
|
10 , |
|
400 ); |
|
INSERT INTO item VALUES( |
504 , |
2 , |
104351 , |
22.42 |
, |
35 |
, |
784.7 ); |
||
INSERT INTO item VALUES( |
504 , |
3 , |
104352 , |
50 |
, |
|
5 , |
|
|
250 ); |
INSERT INTO item VALUES( |
538 , |
1 , |
104351 , |
24.7 , |
|
30 |
, |
|
741 ); |
|
INSERT INTO item VALUES( |
535 , |
1 , |
104352 , |
54 |
, |
|
15 , |
|
810 ); |
|
INSERT INTO item VALUES( |
578 , |
1 , |
104350 , |
41.8 , |
|
40 |
, |
|
1672 ); |
|
INSERT INTO item VALUES( |
578 , |
2 , |
200376 , |
2.04 , |
190 , |
387.6 ); |
||||
INSERT INTO item VALUES( |
578 , |
3 , |
200380 , |
3.6 |
, |
|
60 , |
|
216 ); |
|
INSERT INTO item VALUES( |
534 , |
1 , |
104350 , |
42 |
, |
|
10 , |
|
420 ); |
|
INSERT INTO item VALUES( |
536 , |
1 , |
104350 , |
39.89 |
, |
40 |
, |
1595.6 ); |
||
INSERT INTO item VALUES( |
536 , |
2 , |
104352 , |
54 |
, |
|
10 , |
|
540 ); |
|
INSERT INTO item VALUES( |
572 , |
1 , |
104351 , |
24.7 , |
|
25 , |
617.5 ); |
|||
INSERT INTO item VALUES( |
572 , |
2 , |
104352 , |
58.3 , |
|
10 |
, |
|
583 ); |
|
INSERT INTO item VALUES( |
514 , |
1 , |
104350 , |
38 |
, |
|
30 , |
|
1140 ); |
|
INSERT INTO item VALUES( |
521 , |
1 , |
104350 , |
40 |
, |
|
20 , |
|
800 ); |
|
85
INSERT INTO item VALUES( |
521 , |
2 , |
102134 , |
3.23 , |
30 |
, |
96.9 ); |
|||
INSERT INTO item VALUES( |
551 , |
1 , |
104350 , |
44 |
, |
|
10 , |
|
440 ); |
|
INSERT INTO item VALUES( |
551 , |
2 , |
104361 , |
50 |
, |
|
5 , |
|
|
250 ); |
INSERT INTO item VALUES( |
551 , |
3 , |
102134 , |
3.4 |
, |
|
20 , |
|
68 ); |
|
INSERT INTO item VALUES( |
551 , |
4 , |
200376 , |
1.92 , |
|
200 |
, |
384 ); |
||
INSERT INTO item VALUES( |
513 , |
1 , |
104350 , |
40 , |
|
15 , |
|
600 ); |
||
INSERT INTO item VALUES( |
513 , |
2 , |
104351 , |
23.6 , |
|
20 |
, |
|
472 ); |
|
INSERT INTO item VALUES( |
513 , |
3 , |
104362 , |
4.25 , |
|
100 |
, |
425 ); |
||
INSERT INTO item VALUES( |
508 , |
1 , |
104360 , |
36 |
, |
|
20 , |
|
720 ); |
|
INSERT INTO item VALUES( |
508 , |
2 , |
104362 , |
4.5 |
, |
|
80 , |
|
360 ); |
|
INSERT INTO item VALUES( |
515 , |
1 , |
104350 , |
40 |
, |
|
20 , |
|
800 ); |
|
INSERT INTO item VALUES( |
515 , |
2 , |
104351 , |
22.42 |
, |
25 |
, |
560.5 ); |
||
INSERT INTO item VALUES( |
515 , |
3 , |
102134 , |
3.4 |
, |
|
20 , |
|
68 ); |
|
INSERT INTO item VALUES( |
542 , |
1 , |
104350 , |
41.8 , |
25 |
, |
1045 ); |
|||
INSERT INTO item VALUES( |
542 , |
2 , |
104360 , |
39 |
, |
|
20 , |
|
780 ); |
|
INSERT INTO item VALUES( |
542 , |
3 , |
104361 , |
50 |
, |
|
11 , |
|
550 ); |
|
INSERT INTO item VALUES( |
542 , |
4 , |
102134 , |
3.4 |
, |
|
10 , |
|
34 ); |
|
INSERT INTO item VALUES( |
573 , |
1 , |
103120 , |
22.5 , |
|
50 |
, |
|
1125 ); |
|
INSERT INTO item VALUES( |
573 , |
2 , |
103131 , |
4.04 , |
50 , |
202 ); |
||||
INSERT INTO item VALUES( |
573 , |
3 , |
103141 , |
20 |
, |
|
15 , |
|
300 ); |
|
INSERT INTO item VALUES( |
566 , |
1 , |
103120 , |
23.75 |
, |
40 , |
950 ); |
|||
INSERT INTO item VALUES( |
566 , |
2 , |
103121 , |
27 |
, |
|
50 , |
|
1350 ); |
|
INSERT INTO item VALUES( |
566 , |
3 , |
103130 , |
3.77 , |
|
60 |
, |
|
226.2 ); |
|
INSERT INTO item VALUES( |
566 , |
4 , |
103131 , |
3.9 |
, |
|
170 |
, |
|
663 ); |
INSERT INTO item VALUES( |
566 , |
5 , |
103141 , |
20 |
, |
|
10 , |
|
200 ); |
|
INSERT INTO item VALUES( |
520 , |
1 , |
103130 , |
4 , |
|
|
10 , |
|
|
40 ); |
INSERT INTO item VALUES( |
520 , |
2 , |
103131 , |
3.77 , |
|
80 |
, |
|
301.6 ); |
|
INSERT INTO item VALUES( |
519 , |
1 , |
103130 , |
3.5 |
, |
|
110 |
, |
|
385 ); |
INSERT INTO item VALUES( |
519 , |
2 , |
103140 , |
19 |
, |
|
30 , |
|
570 ); |
|
INSERT INTO item VALUES( |
575 , |
1 , |
103140 , |
19 , |
|
30 , |
|
570 ); |
||
INSERT INTO item VALUES( |
575 , |
2 , |
200380 , |
3.8 |
, |
|
30 , |
|
114 ); |
|
INSERT INTO item VALUES( |
547 , |
1 , |
103130 , |
3.77 , |
|
80 |
, |
|
301.6 ); |
|
INSERT INTO item VALUES( |
547 , |
2 , |
103131 , |
4.04 , |
|
70 |
, |
|
282.8 ); |
|
INSERT INTO item VALUES( |
547 , |
3 , |
103140 , |
20 |
, |
|
15 , |
|
300 ); |
|
INSERT INTO item VALUES( |
547 , |
4 , |
103141 , |
20 |
, |
|
5 , |
|
|
100 ); |
INSERT INTO item VALUES( |
540 , |
1 , |
103130 , |
3.98 , |
|
40 |
, |
|
159.2 ); |
|
INSERT INTO item VALUES( |
540 , |
2 , |
103131 , |
3.9 |
, |
|
180 |
, |
|
702 ); |
INSERT INTO item VALUES( |
567 , |
1 , |
103140 , |
20 |
, |
|
10 , |
|
200 ); |
|
INSERT INTO item VALUES( |
570 , |
1 , |
103131 , |
4.04 , |
|
90 |
, |
|
363.6 ); |
|
INSERT INTO item VALUES( |
570 , |
2 , |
103140 , |
20 |
, |
|
15 , |
|
300 ); |
|
INSERT INTO item VALUES( |
571 , |
1 , |
103131 , |
4.04 , |
|
70 |
, |
|
282.8 ); |
|
INSERT INTO item VALUES( |
571 , |
2 , |
103140 , |
19 |
, |
|
25 , |
|
475 ); |
|
INSERT INTO item VALUES( |
571 , |
3 , |
102132 , |
3.4 |
, |
|
15 , |
|
51 ); |
|
INSERT INTO item VALUES( |
571 , |
4 , |
200376 , |
2.16 , |
|
80 |
, |
|
172.8 ); |
|
INSERT INTO item VALUES( |
571 , |
5 , |
200380 , |
3.8 |
, |
|
30 , |
|
114 ); |
|
INSERT INTO item VALUES( |
541 , |
1 , |
103140 , |
20 |
, |
|
10 , |
|
200 ); |
|
INSERT INTO item VALUES( |
541 , |
2 , |
103141 , |
20 |
, |
|
10 , |
|
200 ); |
|
INSERT INTO item VALUES( |
532 , |
1 , |
103120 , |
21.59 |
, |
60 , |
1295.4 ); |
|||
INSERT INTO item VALUES( |
527 , |
1 , |
103120 , |
21.59 |
, |
60 |
, |
1295.4 ); |
||
INSERT INTO item VALUES( |
527 , |
2 , |
103121 , |
27.35 |
, |
40 |
, |
1094 ); |
||
INSERT INTO item VALUES( |
527 , |
3 , |
103140 , |
19 |
, |
|
35 , |
|
665 ); |
|
INSERT INTO item VALUES( |
501 , |
1 , |
103130 , |
3.6 |
, |
|
60 , |
|
216 ); |
|
INSERT INTO item VALUES( |
564 , |
1 , |
103130 , |
3.77 , |
|
80 |
, |
|
301.6 ); |
|
INSERT INTO item VALUES( |
564 , |
2 , |
103131 , |
3.9 |
, |
|
120 |
, |
|
468 ); |
INSERT INTO item VALUES( |
564 , |
3 , |
102132 , |
3.23 , |
|
40 |
, |
|
129.2 ); |
|
INSERT INTO item VALUES( |
537 , |
1 , |
103130 , |
3.6 |
, |
|
160 |
, |
|
576 ); |
INSERT INTO item VALUES( |
537 , |
2 , |
102132 , |
3.23 , |
30 , |
96.9 ); |
||||
INSERT INTO item VALUES( |
522 , |
1 , |
103120 , |
21.59 |
, |
80 |
, |
1727.2 ); |
||
INSERT INTO item VALUES( |
522 , |
2 , |
103121 , |
28.8 , |
|
10 , |
288 ); |
|||
INSERT INTO item VALUES( |
522 , |
3 , |
103131 , |
4.04 , |
|
90 |
, |
|
363.6 ); |
|
INSERT INTO item VALUES( |
522 , |
4 , |
103141 , |
20 |
, |
|
10 , |
|
200 ); |
|
INSERT INTO item VALUES( |
554 , |
1 , |
103131 , |
4.04 , |
|
70 |
, |
|
282.8 ); |
|
86
INSERT INTO item VALUES( |
569 , |
1 , |
103120 , |
22.5 , |
50 |
, |
1125 ); |
|||
INSERT INTO item VALUES( |
569 , |
2 , |
103130 , |
3.6 |
, |
|
180 |
, |
|
648 ); |
INSERT INTO item VALUES( |
569 , |
3 , |
103141 , |
20 |
, |
|
15 , |
|
300 ); |
|
INSERT INTO item VALUES( |
550 , |
1 , |
103120 , |
22.5 , |
|
60 , |
1350 ); |
|||
INSERT INTO item VALUES( |
550 , |
2 , |
103140 , |
19 |
, |
|
45 , |
|
855 ); |
|
INSERT INTO item VALUES( |
550 , |
3 , |
103141 , |
20 |
, |
|
5 , |
|
|
100 ); |
INSERT INTO item VALUES( |
550 , |
4 , |
102132 , |
3.4 |
, |
|
15 , |
|
51 ); |
|
INSERT INTO item VALUES( |
548 , |
1 , |
103140 , |
19 |
, |
|
30 , |
|
570 ); |
|
INSERT INTO item VALUES( |
548 , |
2 , |
102132 , |
3.23 , |
|
30 |
, |
|
96.9 ); |
|
INSERT INTO item VALUES( |
505 , |
1 , |
103130 , |
3.5 |
, |
|
100 , |
350 ); |
||
INSERT INTO item VALUES( |
505 , |
2 , |
103140 , |
20 |
, |
|
15 , |
|
300 ); |
|
INSERT INTO item VALUES( |
577 , |
1 , |
103121 , |
30 |
, |
|
20 , |
|
600 ); |
|
INSERT INTO item VALUES( |
577 , |
2 , |
103131 , |
3.9 |
, |
|
150 |
, |
|
585 ); |
INSERT INTO item VALUES( |
577 , |
3 , |
102132 , |
3.23 , |
25 |
, |
80.75 ); |
|||
INSERT INTO item VALUES( |
533 , |
1 , |
103121 , |
27.35 |
, |
30 |
, |
820.5 ); |
||
INSERT INTO item VALUES( |
533 , |
2 , |
103130 , |
3.77 , |
|
80 |
, |
|
301.6 ); |
|
INSERT INTO item VALUES( |
561 , |
1 , |
103120 , |
22.5 , |
|
80 |
, |
|
1800 ); |
|
INSERT INTO item VALUES( |
561 , |
2 , |
103130 , |
3.77 , |
|
70 |
, |
|
263.9 ); |
|
INSERT INTO item VALUES( |
561 , |
3 , |
103141 , |
20 , |
|
15 , |
|
300 ); |
||
INSERT INTO item VALUES( |
561 , |
4 , |
200376 , |
2.16 , |
|
90 |
, |
|
194.4 ); |
|
INSERT INTO item VALUES( |
506 , |
1 , |
103120 , |
20.88 |
, |
80 |
, |
1670.4 ); |
||
INSERT INTO item VALUES( |
506 , |
2 , |
103130 , |
3.5 |
, |
|
180 |
, |
|
630 ); |
INSERT INTO item VALUES( |
506 , |
3 , |
103141 , |
20 |
, |
|
15 , |
|
300 ); |
|
INSERT INTO item VALUES( |
530 , |
1 , |
103121 , |
25.91 |
, |
50 |
, |
1295.5 ); |
||
INSERT INTO item VALUES( |
530 , |
2 , |
103130 , |
3.6 |
, |
|
200 |
, |
|
720 ); |
INSERT INTO item VALUES( |
530 , |
3 , |
103140 , |
19 |
, |
|
40 , |
|
760 ); |
|
INSERT INTO item VALUES( |
530 , |
4 , |
103141 , |
20 |
, |
|
10 , |
|
200 ); |
|
INSERT INTO item VALUES( |
530 , |
5 , |
102132 , |
3.4 |
, |
|
15 , |
|
51 ); |
|
INSERT INTO item VALUES( |
545 , |
1 , |
103140 , |
19 , |
|
25 , |
|
475 ); |
||
INSERT INTO item VALUES( |
557 , |
1 , |
103120 , |
23.75 |
, |
40 |
, |
950 ); |
||
INSERT INTO item VALUES( |
557 , |
2 , |
103121 , |
30 |
, |
|
10 , |
|
300 ); |
|
INSERT INTO item VALUES( |
557 , |
3 , |
103140 , |
19 |
, |
|
35 , |
|
665 ); |
|
INSERT INTO item VALUES( |
557 , |
4 , |
200376 , |
2.16 , |
|
80 |
, |
|
172.8 ); |
|
INSERT INTO item VALUES( |
557 , |
5 , |
200380 , |
3.4 |
, |
|
110 |
, |
|
374 ); |
INSERT INTO item VALUES( |
546 , |
1 , |
103120 , |
22.5 , |
|
50 , |
1125 ); |
|||
INSERT INTO item VALUES( |
546 , |
2 , |
103121 , |
30 |
, |
|
20 , |
|
600 ); |
|
INSERT INTO item VALUES( |
546 , |
3 , |
103130 , |
3.6 |
, |
|
120 |
, |
|
432 ); |
INSERT INTO item VALUES( |
546 , |
4 , |
103131 , |
3.9 |
, |
|
140 |
, |
|
546 ); |
INSERT INTO item VALUES( |
546 , |
5 , |
103140 , |
19 |
, |
|
40 , |
|
760 ); |
|
INSERT INTO item VALUES( |
546 , |
6 , |
103141 , |
20 |
, |
|
10 , |
|
200 ); |
|
INSERT INTO item VALUES( |
510 , |
1 , |
103120 , |
20.88 |
, |
60 |
, |
1252.8 ); |
||
INSERT INTO item VALUES( |
510 , |
2 , |
103131 , |
4.19 , |
|
20 |
, |
|
83.8 ); |
|
CREATE TABLE price(
product_id NUMBER(6,0) NOT NULL, list_price NUMBER(8,2) NULL, min_price NUMBER(8,2) NULL, start_date DATE NOT NULL, end_date DATE NULL,
CHECK (product_id IS NOT NULL),
CHECK (start_date IS NOT NULL),
CHECK (list_price IS NULL OR min_price IS NULL OR min_price <= list_price), CHECK (end_date IS NULL OR start_date <= end_date),
PRIMARY KEY (product_id, start_date),
FOREIGN KEY (product_id) REFERENCES product(product_id) );
INSERT INTO price VALUES( |
100871 , |
5 , |
3.20 , '01-JAN-1989' , |
'01-DEC-1989' ); |
||
INSERT INTO price VALUES( |
100890 , |
58 , |
46.40 |
, '01-JAN-1989' |
, '' |
); |
INSERT INTO price VALUES( |
100890 , |
54 , |
40.50 |
, '01-JUN-1988' |
, '31-DEC-1988' ); |
|
INSERT INTO price VALUES( |
100860 , |
35 , |
28.00 , '01-JUN-1990' |
, '' |
); |
|
87
INSERT INTO price VALUES( |
100860 , |
32 , |
25.60 |
, '01-JAN-1990' |
, |
'31-MAY-1990' ); |
||
INSERT INTO price VALUES( |
100860 , |
30 , |
24.00 |
, '01-JAN-1989' |
, |
'31-DEC-1989' ); |
||
INSERT INTO price VALUES( |
100861 , |
45 , |
36.00 |
, '01-JUN-1990' |
, |
'' |
); |
|
INSERT INTO price VALUES( |
100861 , |
42 , |
33.60 |
, '01-JAN-1990' |
, |
'31-MAY-1990' ); |
||
INSERT INTO price VALUES( |
100861 , |
39 , |
31.20 , '01-JAN-1989' |
, '31-DEC-1989' ); |
||||
INSERT INTO price VALUES( |
100870 , |
3 , |
2.40 |
, '01-JAN-1990' , |
'' |
|
); |
|
INSERT INTO price VALUES( |
100870 , |
2 , |
1.90 |
, '01-JAN-1989' , |
'01-DEC-1989' ); |
|||
INSERT INTO price VALUES( |
100871 , |
6 , |
4.80 |
, '01-JAN-1990' , |
'' |
|
); |
|
INSERT INTO price VALUES( |
101860 , |
24 , |
18.00 |
, '15-FEB-1989' , '' |
); |
|||
INSERT INTO price VALUES( |
101863 , |
13 , |
9.40 , '15-FEB-1989' , '' |
); |
||||
INSERT INTO price VALUES( |
102130 , |
3 , |
2.80 |
, '18-AUG-1989' , '' |
); |
|||
INSERT INTO price VALUES( |
200376 , |
2 , |
1.75 |
, '15-NOV-1990' , '' |
); |
|||
INSERT INTO price VALUES( |
200380 , |
4 , |
3.20 |
, '15-NOV-1990' , '' |
); |
|||
INSERT INTO price VALUES( |
103120 , |
23 , |
18.60 |
, '01-JAN-1989' , '31-DEC-1989' ); |
||||
INSERT INTO price VALUES( |
103120 , |
24 , |
19.20 |
, '01-JAN-1990' , '31-MAY-1990' ); |
||||
INSERT INTO price VALUES( |
103120 , |
25 , |
20.00 |
, '01-JUN-1990' , '' |
); |
|||
INSERT INTO price VALUES( |
103121 , |
28 , |
22.30 |
, '01-JAN-1989' , '31-DEC-1989' ); |
||||
INSERT INTO price VALUES( |
103121 , |
29 , |
23.00 |
, '01-JAN-1990' , '31-MAY-1990' ); |
||||
INSERT INTO price VALUES( |
103121 , |
30 , |
24.00 |
, '01-JUN-1990' , '' |
); |
|||
INSERT INTO price VALUES( |
103130 , |
4 , |
3.50 |
, '01-JAN-1989' , '31-DEC-1989' ); |
||||
INSERT INTO price VALUES( |
103130 , |
4 , |
3.60 |
, '01-JAN-1990' , '' |
|
); |
||
INSERT INTO price VALUES( |
103131 , |
4 , |
3.60 |
, '01-JAN-1989' , '31-DEC-1989' ); |
||||
INSERT INTO price VALUES( |
103131 , |
5 , |
3.90 |
, '01-JAN-1990' , '' |
|
); |
||
INSERT INTO price VALUES( |
103140 , |
20 , |
15.00 |
, '15-FEB-1989' , '' |
); |
|||
INSERT INTO price VALUES( |
103141 , |
20 , |
15.00 |
, '15-FEB-1989' , '' |
); |
|||
INSERT INTO price VALUES( |
102132 , |
3 , |
2.80 |
, '18-AUG-1989' , '' |
); |
|||
INSERT INTO price VALUES( |
104350 , |
40 , |
32.00 |
, '01-JAN-1989' , '31-DEC-1989' ); |
||||
INSERT INTO price VALUES( |
104350 , |
42 , |
33.60 |
, '01-JAN-1990' , '31-MAY-1990' ); |
||||
INSERT INTO price VALUES( |
104350 , |
44 , |
35.30 |
, '01-JUN-1990' , '' |
); |
|||
INSERT INTO price VALUES( |
104351 , |
24 , |
18.90 |
, '01-JAN-1989' , '31-DEC-1989' ); |
||||
INSERT INTO price VALUES( |
104351 , |
25 , |
19.80 |
, '01-JAN-1990' , '31-MAY-1990' ); |
||||
INSERT INTO price VALUES( |
104351 , |
26 , |
20.80 |
, '01-JUN-1990' , '' |
); |
|||
INSERT INTO price VALUES( |
104352 , |
50 , |
35.00 |
, '01-JAN-1989' , '31-DEC-1989' ); |
||||
INSERT INTO price VALUES( |
104352 , |
54 , |
37.80 |
, '01-JAN-1990' , '31-MAY-1990' ); |
||||
INSERT INTO price VALUES( |
104352 , |
58 , |
40.80 |
, '01-JUN-1990' , '' |
); |
|||
INSERT INTO price VALUES( |
104360 , |
36 , |
29.50 |
, '01-JAN-1989' , '31-DEC-1989' ); |
||||
INSERT INTO price VALUES( |
104360 , |
39 , |
32.00 |
, '01-JAN-1990' , '' |
); |
|||
INSERT INTO price VALUES( |
104361 , |
47 , |
33.00 |
, '01-JAN-1989' , '31-DEC-1989' ); |
||||
INSERT INTO price VALUES( |
104361 , |
50 , |
35.00 |
, '01-JAN-1990' , '' |
); |
|||
INSERT INTO price VALUES( |
104362 , |
5 , |
4.00 |
, '15-FEB-1989' , '' |
|
); |
||
INSERT INTO price VALUES( |
102134 , |
3 , |
2.80 |
, '16-AUG-1989' , '' |
); |
|||
INSERT INTO price VALUES( |
105123 , |
36 , |
29.00 |
, '01-JAN-1989' , '31-DEC-1989' ); |
||||
INSERT INTO price VALUES( |
105123 , |
38 , |
32.00 |
, '01-JAN-1990' , '31-MAY-1990' ); |
||||
INSERT INTO price VALUES( |
105123 , |
40 , |
34.00 |
, '01-JUN-1990' , '' |
); |
|||
INSERT INTO price VALUES( |
105124 , |
10 , |
7.60 , '01-JAN-1989' , '31-DEC-1989' ); |
|||||
INSERT INTO price VALUES( |
105124 , |
12 , |
9.00 , '01-JAN-1990' , '31-MAY-1990' ); |
|||||
INSERT INTO price VALUES( |
105124 , |
15 , |
10.00 |
, '01-JUN-1990' , '' |
); |
|||
INSERT INTO price VALUES( |
105125 , |
2 , |
1.70 |
, '01-JAN-1989' , '31-DEC-1989' ); |
||||
INSERT INTO price VALUES( |
105125 , |
3 , |
2.60 |
, '01-JAN-1990' , '' |
|
); |
||
INSERT INTO price VALUES( |
105126 , |
5 , |
4.30 |
, '01-JAN-1989' , '31-DEC-1989' ); |
||||
INSERT INTO price VALUES( |
105126 , |
6 , |
5.00 |
, '01-JAN-1990' , '' |
|
); |
||
INSERT INTO price VALUES( |
105127 , |
6 , |
5.00 |
, '15-NOV-1990' , '' |
); |
|||
INSERT INTO price VALUES( |
105128 , |
10 , |
8.00 , '15-NOV-1990' , '' |
); |
||||
INSERT INTO price VALUES( |
102136 , |
3 , |
2.80 |
, '03JUN -1989' , '' |
); |
|||
CREATE TABLE salary_grade( grade_id NUMBER(3,0) NOT NULL, lower_bound NUMBER(7,2) NULL, upper_bound NUMBER(7,2) NULL, CHECK (grade_id IS NOT NULL),
88
CHECK ((lower_bound IS NULL AND upper_bound IS NOT NULL) OR (lower_bound IS NOT NULL AND upper_bound IS NULL)
OR (lower_bound <= upper_bound)), PRIMARY KEY (grade_id)
);
INSERT INTO salary_grade VALUES( |
1 , |
700 , |
1200 ); |
INSERT INTO salary_grade VALUES( |
2 , |
1201 , |
1400 ); |
INSERT INTO salary_grade VALUES( |
3 , |
1401 , |
2000 ); |
INSERT INTO salary_grade VALUES( |
4 , |
2001 , |
3000 ); |
INSERT INTO salary_grade VALUES( |
5 , |
3001 , |
9999 ); |
commit;
Приложение 2.
Содержание таблиц пользователя DEMO
DEMO@ORCL>SELECT * FROM location;
LOCATION_ID REGIONAL_GROUP
----------- --------------------
168MOSCOW
169KIEV
122 NEW YORK
124 DALLAS
123 CHICAGO
167 BOSTON
DEMO@ORCL> SELECT * FROM department; |
|
DEPARTMENT_ID NAME |
LOCATION_ID |
------------- -------------- ----------- |
|
55 SALES |
|
56 OPERATIONS |
|
10 ACCOUNTING |
122 |
20 RESEARCH |
124 |
30 SALES |
123 |
40 OPERATIONS |
167 |
12 RESEARCH |
122 |
13 SALES |
122 |
14 OPERATIONS |
122 |
23 SALES |
124 |
24 OPERATIONS |
124 |
34 OPERATIONS |
123 |
43 SALES |
167 |
11 rows selected. DEMO@ORCL>SELECT * FROM job;
JOB_ID FUNCTION
---------- ------------------------------
667CLERK
668STAFF
669ANALYST
670SALESPERSON
671MANAGER
672PRESIDENT
6 rows selected.
DEMO@ORCL>SELECT * FROM employee;
EMPLOYEE_ID |
FIRST_NAME JOB_ID |
HIRE_DATE |
COMMISSION |
|
|
|
|
|
|
|
|
89 |
LAST_NAME |
M |
|
MANAGER_ID |
SALARY |
DEPARTMENT_ID |
|||
----------- --------------- --------------- |
- |
---------- ---------- |
----------- |
---------- |
---------- |
------------- |
||
7839 KING |
FRANCIS |
A |
672 |
|
17-nov-1985 |
5000 |
|
10 |
7505 DOYLE |
JEAN |
K |
671 |
7839 |
04-apr-1985 |
2850 |
0 |
13 |
7555 PETERS |
DANIEL |
T |
670 |
7505 |
31-mar-1985 |
1250 |
300 |
13 |
7557 SHAW |
KAREN |
P |
670 |
7505 |
02-apr-1985 |
1250 |
1200 |
13 |
7600 PORTER |
RAYMOND Y |
670 |
7505 |
15-apr-1985 |
1250 |
900 |
13 |
|
7820 ROSS |
PAUL |
S |
670 |
7 505 |
01-jun-1985 |
1300 |
800 |
13 |
7950 JENSEN |
ALICE |
B |
667 |
7505 |
15-jan-1987 |
750 |
0 |
13 |
7506 DENNIS |
LYNN |
S |
671 |
7839 |
15-may-1985 |
2750 |
0 |
23 |
7560 DUNCAN |
SARAH |
S |
670 |
7506 |
31-may-1985 |
1250 |
0 |
23 |
7564 LANGE |
GREGORY |
J |
670 |
7506 |
01-jun-1985 |
1250 |
300 |
23 |
7789 WEST |
LIVIA |
N |
670 |
7506 |
04-apr-1985 |
1500 |
1000 |
23 |
7954 MURRAY |
JAMES |
T |
667 |
7506 |
16-jan-1987 |
750 |
0 |
23 |
7507 BAKER |
LESLIE |
D |
671 |
7839 |
10-jun-1985 |
2200 |
0 |
14 |
7609 LEWIS |
RICHARD |
M |
668 |
7507 |
16-apr-1985 |
1800 |
0 |
14 |
7676 SOMMERS DENISE |
D |
668 |
7507 |
19-apr-1985 |
1850 |
0 |
14 |
|
7566 JONES |
TERRY |
M |
671 |
7839 |
02-apr-1985 |
2975 |
0 |
20 |
7788 SCOTT |
DONALD |
T |
669 |
7566 |
09-dec-1986 |
3000 |
0 |
20 |
7876 ADAMS |
DIANE |
G |
667 |
7788 |
12-jan-1987 |
1100 |
0 |
20 |
7902 FORD |
JENNIFER |
D |
669 |
7566 |
03-dec-1985 |
3000 |
0 |
20 |
7369 SMITH |
JOHN |
Q |
667 |
7902 |
17-dec-1984 |
800 |
0 |
20 |
7569 ALBERTS |
CHRIS |
L |
671 |
7839 |
06-apr-1985 |
3000 |
0 |
12 |
7799 FISHER |
MATTHEW G |
669 |
7569 |
12-dec-1986 |
3000 |
0 |
12 |
|
7919 DOUGLAS MICHAEL |
A |
667 |
7799 |
04-jan-1987 |
800 |
0 |
12 |
|
7916 ROBERTS |
GRACE |
M |
669 |
7569 |
04-jan-1987 |
2875 |
0 |
12 |
7698 BLAKE |
MARION |
S |
671 |
7839 |
01-may-1985 |
2850 |
0 |
30 |
7499 ALLEN |
KEVIN |
J |
670 |
7698 |
20-feb-1985 |
1600 |
300 |
30 |
7521 WARD |
CYNTHIA |
D |
670 |
7698 |
22-feb-1985 |
1250 |
500 |
30 |
7654 MARTIN |
KENNETH |
J |
670 |
7698 |
28-sep-1985 |
1250 1400 |
30 |
|
7844 TURNER |
MARY |
A |
670 |
7698 |
08-sep-1985 |
1500 |
0 |
30 |
7900 JAMES |
FRED |
S |
667 |
7698 |
03-dec-1985 |
950 |
0 |
30 |
7782 CLARK |
CAROL |
F |
671 |
7839 |
09-jun-1985 |
2450 |
0 |
10 |
7934 MILLER |
BARBARA |
M |
667 |
7782 |
23-jan-1986 |
1300 |
0 |
10 |
32 rows selected.
90
DEMO@ORCL>SELECT * FROM customer; |
|
|
|
|
|
|
|
|
||
CUSTOMER_ID |
ADDRESS |
CITY |
ST |
ZIP_CODE |
PHONE_NUMBER |
CREDIT_LIMIT |
||||
|
NAME |
|
|
|
|
AREA_CODE |
SALESPERSON_ID |
|
||
----- |
---------------- |
---------------------- |
---------------- |
--- |
------ |
------------ |
|
------------ |
----------- |
----------- |
100 |
JOCKSPORTS |
345 VIEWRIDGE |
BELMONT |
CA |
96711 |
415 |
|
5986609 |
7844 |
5000 |
101 |
TKB SPORT SHOP |
490 BOLI RD. |
REDWOOD CITY CA |
94061 |
415 |
|
3681223 |
7521 |
10000 |
|
102 |
VOLLYRITE |
9722 HAMILTON |
BURLINGAME |
CA |
95133 |
415 |
|
6443341 |
7654 |
7000 |
103 |
JUST TENNIS |
HILLVIEW MALL |
BURLINGAME |
CA |
97 |
415 |
|
6779312 |
7521 |
3000 |
104 |
EVERY MOUNTAIN |
574 SURRY RD. |
CUPERTINO |
CA |
93301 |
408 |
|
9962323 |
7499 |
10000 |
105 |
K + T SPORTS |
3476 EL PASEO |
SANTA CLARA |
CA |
91003 |
408 |
|
3769966 |
7844 |
5000 |
106 |
SHAPE UP |
908 SEQUOIA |
PALO ALTO |
CA |
94301 |
415 |
|
3649777 |
7521 |
6000 |
107 |
WOMENS SPORTS |
VALCO VILLAGE |
SUNNYVALE |
CA |
93301 |
408 |
|
9674398 |
7499 |
10000 |
108 |
NORTH WOODS HEALTH |
98 LONE PINE WAY |
HIBBING |
MN |
55649 |
612 |
|
5669123 |
7844 |
8000 |
201 STADIUM SPORTS |
47 IRVING PL. |
NEW YORK |
NY |
10003 |
212 |
|
5555335 |
7557 |
10000 |
|
202 |
HOOPS |
2345 ADAMS AVE. |
LEICESTER |
MA |
01524 |
508 |
|
5557542 |
7820 |
5000 |
203 REBOUND SPORTS |
2 E. 14TH ST. |
NEW YORK |
NY |
10009 |
212 |
|
5555989 |
7557 |
10000 |
|
204 THE POWER FORWARD |
1 KNOTS LANDING |
DALLAS |
TX |
75248 |
214 |
|
5550505 |
7560 |
12000 |
|
205 POINT GUARD |
20 THURSTON ST. |
YONKERS |
NY |
10956 |
914 |
|
5554766 |
7557 |
3000 |
|
206 THE COLISEUM |
5678 WILBUR PL. |
SCARSDALE |
NY |
10583 |
914 |
|
5550217 |
7557 |
6000 |
|
207 FAST BREAK |
1000 HERBERT LN. |
CONCORD |
MA |
01742 |
508 |
|
5551298 |
7820 |
7000 |
|
208 AL AND BOB'S SPORTS |
260 YORKTOWN CT. |
AUSTIN |
TX |
78731 |
512 |
|
5557631 |
7560 |
4000 |
|
211 AT BAT |
234 BEACHEM ST. |
BROOKLINE |
MA |
02146 |
617 |
|
5557385 |
7820 |
8000 |
|
212 ALL SPORT |
1000 38TH ST. |
BROOKLYN |
NY |
11210 |
718 |
|
5551739 |
7600 |
6000 |
|
213 GOOD SPORT |
400 46TH ST. |
SUNNYSIDE |
NY |
11104 |
718 |
|
5553771 |
7600 |
5000 |
|
214 AL'S PRO SHOP |
45 SPRUCE ST. |
SPRING |
TX |
77388 |
713 |
|
5555172 |
7564 |
8000 |
|
215 BOB'S FAMILY SPORTS |
400 E. 23RD |
HOUSTON |
TX |
77026 |
713 |
|
5558015 |
7654 |
8000 |
|
216 THE ALL AMERICAN |
547 PRENTICE RD. |
CHELSEA |
MA |
02150 |
617 |
|
5553047 |
7820 |
5000 |
|
217 HIT, THROW, AND RUN |
333 WOOD COURT |
GRAPEVINE |
TX |
76051 |
817 |
|
5552352 |
7564 |
6000 |
|
218 THE OUTFIELD |
346 GARDEN BLVD. |
FLUSHING |
NY |
11355 |
718 |
|
5552131 |
7820 |
4000 |
|
221 WHEELS AND DEALS |
2 MEMORIAL DRIVE |
HOUSTON |
TX |
77007 |
713 |
|
5554139 |
7789 |
10000 |
|
222 JUST BIKES |
4000 PARKRIDGE BLVD. DALLAS |
TX |
75205 |
214 |
|
5558735 |
7789 |
4000 |
||
223 VELO SPORTS |
23 WHITE ST. |
MALDEN |
MA |
02148 |
617 |
|
5554983 |
7820 |
5000 |
|
224 JOE'S BIKE SHOP |
4500 FOX COURT |
GRAND PRARI E TX |
75051 |
214 |
|
5559834 |
7789 |
6000 |
||
225 BOB'S SWIM, AND RUN |
300 HORSECREEK |
IRVING |
TX |
75039 |
214 |
|
5558388 |
7789 |
7000 |
|
226 CENTURY SHOP |
8 DAGMAR DR. |
HUNTINGTON |
NY |
11743 |
516 |
|
5553006 |
7555 |
4000 |
|
227 THE TOUR |
2500 GARDNER RD. |
SOMERVILLE |
MA |
02144 |
617 |
|
5556673 |
7820 |
5000 |
|
228 FITNESS FIRST |
5000 85TH ST. |
JACKSON HEIGHTS |
NY 11372 |
718 |
|
5558710 |
7555 |
4000 |
||
33 rows selected. |
|
|
|
|
|
|
|
|
|
|