/
demo.txt
176 lines (158 loc) · 6.18 KB
/
demo.txt
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
# Martin Smith, Eric Anderson
# CSCI 330 SURLY2 Demo Input File
# SURLY COMMAND FILE CONTAINING THE DEMO FRUIT STAND ABOUT TO GO INTO BUSINESS!!
#RELATION
#DEFINITIONS
RELATION FRUIT (F_ID CHAR 1, FNUM CHAR 3, FTITLE CHAR 30, FPRICE NUM 4, NUMINSTOCK NUM 4, FSEASON CHAR 6);
RELATION VEGETABLE (V_ID CHAR 1, VNUM CHAR 3, VTITLE CHAR 30, VPRICE NUM 4, NUMINSTOCK NUM 4, VSEASON CHAR 6);
RELATION DRINK (D_ID CHAR 1, DNUM CHAR 3, DNAME CHAR 30, DPRICE NUM 4, GALINSTOCK NUM 4, TASTESCORE NUM 1);
RELATION PURCHASE (PURCHASER CHAR 15, PROD_ID CHAR 1, PROD_NUM CHAR 3, QUANTITY NUM 4, MONTH NUM 2, YEAR NUM 4);
RELATION STORE (SID CHAR 4,
STORE CHAR 20,
STATE CHAR 2,
OPENS NUM 4,
CLOSES NUM 4,
DAYS CHAR 7);
RELATION STATE (STATENAME CHAR 15, STATEABBREV CHAR 2);
RELATION EMPLOYEE (FNAME CHAR 16, LNAME CHAR 16, HOME CHAR 2, GENDER CHAR 1, YRS_EXP NUM 2, INCH_HEIGHT NUM 2, FAVSPORT CHAR 22);
RELATION INVALID (BLAH BLAH BLAH BLAH BLAH);
#INSERTING TUPLES
INSERT FRUIT F A1 APPLE 3 250 SPRING;
INSERT FRUIT F B1 BANANA 1 465 SPRING;
INSERT FRUIT F G2 GRAPE 3 730 SUMMER;
INSERT FRUIT F O3 ORANGE 2 350 AUTUMN;
INSERT FRUIT F S8 STRAWBERRY 4 185 SUMMER;
INSERT FRUIT F B2 BLUEBERRY 5 185 SUMMER;
INSERT FRUIT F B3 BLACKBERRY 5 185 SUMMER;
INSERT FRUIT F C1 RAINIER CHERRY 10 102 SUMMER;
INSERT FRUIT F P9 POMEGRANATE 7 50 WINTER;
INSERT VEGETABLE V C1 CARROT 2 440 SUMMER;
INSERT VEGETABLE V T5 TOMATO 2 900 AUTUMN;
INSERT VEGETABLE V P3 PUMPKIN 9 53 AUTUMN;
INSERT VEGETABLE V B1 'BRUSSELS SPROUT' 3 245 WINTER;
INSERT VEGETABLE V S1 LETTUCE 4 100 ANY;
INSERT DRINK D L2 LEMONADE 1 100 9;
INSERT DRINK D L4 LIMEADE 1 100 2;
INSERT DRINK D A4 'APPLE JUICE' 2 133 6;
INSERT DRINK D M5 'COCONUT MILK' 3 40 5;
INSERT DRINK D M6 'SKIM MILK' 3 41 4;
INSERT DRINK D W1 WATER 4 500 7;
INSERT PURCHASE HAT V B1 150 6 2017;
INSERT PURCHASE ANDERSON V B1 5 7 2017;
INSERT PURCHASE ANDERSON V B1 90 7 2017 ;
INSERT PURCHASE HAT V S1 100 3 2018;
INSERT PURCHASE ONE V T5 250 8 2017;
INSERT PURCHASE TWO V T5 350 10 2017;
INSERT PURCHASE THREE V T5 400 12 2017;
INSERT PURCHASE HAT V C1 400 3 2018;
INSERT PURCHASE SMITH V C1 40 1 2008;
INSERT PURCHASE ANDERSON F A1 150 9 2017;
INSERT PURCHASE ANDERSON F A1 50 9 2017;
INSERT PURCHASE SMITH F A1 100 9 2009;
INSERT PURCHASE FISH F B2 465 10 2018;
INSERT PURCHASE FOUR F G2 30 4 2016;
INSERT PURCHASE THREE F G2 450 2 2016;
INSERT PURCHASE TWO F G2 15 2 2016;
INSERT PURCHASE TWO F G2 3 2 2016;
INSERT PURCHASE TWO F G2 2 2 2016;
INSERT PURCHASE ONE F G2 230 2 2016;
INSERT PURCHASE FISH F O3 240 5 2018;
INSERT PURCHASE ANDERSON F O3 110 1 2018;
INSERT PURCHASE HAT F S8 85 8 2018;
INSERT PURCHASE HAT F S8 100 9 2018;
INSERT PURCHASE HAT F B2 85 8 2018;
INSERT PURCHASE HAT F B2 100 9 2018;
INSERT PURCHASE HAT F C1 85 8 2018;
INSERT PURCHASE HAT F C1 100 9 2018;
INSERT PURCHASE SMITH F P9 25 7 2017;
INSERT PURCHASE FISH F P3 10 3 2017;
INSERT PURCHASE THREE F P3 5 12 2016;
INSERT PURCHASE TWO F P3 5 11 2016;
INSERT PURCHASE ONE F P3 5 11 2016;
INSERT PURCHASE TWAIN D L2 95 1 2015;
INSERT PURCHASE TWAIN D L2 5 2 2015;
INSERT PURCHASE TWAIN D L4 95 3 2016;
INSERT PURCHASE TWAIN D L4 5 4 2016;
INSERT PURCHASE TWAIN D A4 74 5 2015;
INSERT PURCHASE TWAIN D A4 26 6 2016;
INSERT PURCHASE TWAIN D A4 29 6 2017;
INSERT PURCHASE TWAIN D A4 4 6 2019;
INSERT PURCHASE TWAIN D M5 40 9 1873;
INSERT PURCHASE TWAIN D W1 140 8 1889;
INSERT PURCHASE TWAIN D W1 140 2 1898;
INSERT PURCHASE TWAIN D W1 140 5 1902;
INSERT PURCHASE TWAIN D W1 80 9 1909;
INSERT PURCHASE TWAIN D M6 1 5 2019;
INSERT PURCHASE ANDERSON D M6 30 5 2019;
INSERT PURCHASE SMITH D M6 10 6 2019;
INSERT STORE 2675 'FRED MEYER' WA 0700 2300 MTWRFSU;
INSERT STORE 2676 'FRED MEYER' WA 0600 2330 MTWRFSU;
INSERT STORE 2950 'TRADER JOES' WA 0845 2100 MTWRF;
INSERT STORE 2150 'WHOLE FOODS' WA 1100 1600 MTWRF;
INSERT STORE 2732 WALMART WA 0900 1900 MSU;
INSERT STORE 3732 WALMART OR 0600 2000 TSU;
INSERT STORE 4732 WALMART ID 0530 2030 WSU;
INSERT STORE 5732 WALMART AK 0500 2100 RSU;
INSERT STORE 6732 WALMART UT 0532 2253 FSU;
INSERT STORE 2333 'FARMERS MARKET' WA 0730 1745 SU;
INSERT STORE 3040 COSTCO OR 0000 2359 MWFS;
INSERT STORE 4040 COSTCO ID 0000 2359 MWFU;
INSERT STORE 2999 'MILLER MARKET' WA 0800 1800 MTWRF;
INSERT STATE WASHINGTON WA;
INSERT STATE IDAHO ID;
INSERT STATE OREGON OR;
INSERT STATE ALASKA AK;
INSERT STATE UTAH UT;
INSERT STATE 'SOUTH DAKOTA' SD;
INSERT EMPLOYEE MARK TWAIN ID M 60 72 WRITING;
INSERT EMPLOYEE DOCTOR SEUSS ID M 42 74 HORSEBACK;
INSERT EMPLOYEE 'CAT IN THE' HAT AK ? 25 90 SOCCER;
INSERT EMPLOYEE THING ONE AK ? 5 35 'DUMPSTER DIVING';
INSERT EMPLOYEE THING TWO AK ? 5 35 'DUMPSTER DIVING';
INSERT EMPLOYEE THING THREE AK ? 6 32 HOCKEY;
INSERT EMPLOYEE THING FOUR AK ? 2 39 RUGBY;
INSERT EMPLOYEE FISH FISH WA ? 10 6 SWIMMING;
INSERT EMPLOYEE SALLY SOMETHING WA F 0 52 BASEBALL;
INSERT EMPLOYEE MARTIN SMITH WA M 2 70 SOCCER;
INSERT EMPLOYEE ERIC ANDERSON SD M 1 70 FOOTBALL;
INSERT INVALID TWO PLUS TWO;
#PRINTING TUPLES
PRINT CATALOG;
PRINT FRUIT, VEGETABLE, DRINK, PURCHASE, STORE, EMPLOYEE;
#SELECT WHERE EXAMPLES
S1 = SELECT STORE WHERE STATE = 'WA';
S2 = SELECT EMPLOYEE WHERE GENDER != '?' and INCH_HEIGHT >= 72;
PRINT S1, S2;
#OVERWRITE WITH EMPTY RELATION
S1 = SELECT PURCHASE WHERE MONTH > 13 or YEAR < 1850;
PRINT S1;
#PROJECT EXAMPLES
P1 = PROJECT PROD_ID, YEAR FROM PURCHASE;
P2 = PROJECT F_ID, FSEASON, F_ID, FSEASON FROM FRUIT;
# DISTINCT TUPLES
P3 = PROJECT STORE FROM STORE;
PRINT P1, P2, P3;
#OVERWRITE WITH INVALID RELATION
P1 = PROJECT NOTHING FROM NOTHING;
PRINT P1;
#JOIN EXAMPLES
J1 = JOIN PURCHASE, EMPLOYEE ON PURCHASE.PURCHASER = EMPLOYEE.LNAME;
J1P = PROJECT FNAME, PURCHASER, PROD_ID, QUANTITY, YRS_EXP FROM J1;
J1PS = SELECT J1P WHERE FNAME = 'THING' and PROD_ID != 'V' and QUANTITY > 200 or PURCHASER = 'HAT' and QUANTITY > 200;
PRINT CATALOG, J1PS;
J2 = JOIN STORE, STATE ON STATE = STATEABBREV;
J2_1 = JOIN J2, EMPLOYEE ON J2.STATE = HOME;
J2_1P = PROJECT STORE, STATE, OPENS, FNAME, LNAME, FAVSPORT FROM J2_1;
J2_1PS = SELECT J2_1P WHERE OPENS < 0800;
PRINT J2_1P, J2_1PS;
#DELETE TUPLES
DELETE DRINK;
DELETE J1P;
DELETE J2_1P, J2_1PS;
DELETE CATALOG;
PRINT FRUIT, DRINK, INVALID;
#DESTROY RELATION
DESTROY STATE;
PRINT CATALOG, STATE;
DESTROY J1PS;
## END ##