This repository has been archived by the owner on Nov 6, 2022. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 0
/
CFG.sql
198 lines (191 loc) · 4.81 KB
/
CFG.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
USE CFG;
CREATE TABLE Rep
(RepNum CHAR(2) PRIMARY KEY,
LastName CHAR(15),
FirstName CHAR(15),
Street CHAR(15),
City CHAR(15),
State CHAR(2),
PostalCode CHAR(5),
Commission DECIMAL(7,2),
Rate DECIMAL(3,2) );
CREATE TABLE Customer
(CustomerNum CHAR(3) PRIMARY KEY,
CustomerName CHAR(35) NOT NULL,
Street CHAR(20),
City CHAR(15),
State CHAR(2),
PostalCode CHAR(5),
Balance DECIMAL(8,2),
CreditLimit DECIMAL(8,2),
RepNum CHAR(2) );
CREATE TABLE Orders
(OrderNum CHAR(5) PRIMARY KEY,
OrderDate DATE,
CustomerNum CHAR(3) );
CREATE TABLE Item
(ItemNum CHAR(4) PRIMARY KEY,
Description CHAR(30),
OnHand DECIMAL(4,0),
Category CHAR(3),
Storehouse CHAR(1),
Price DECIMAL(6,2) );
CREATE TABLE OrderLine
(orderNum CHAR(5),
ItemNum CHAR(4),
NumOrdered DECIMAL(3,0),
QuotedPrice DECIMAL(6,2),
PRIMARY KEY (OrderNum, ItemNum) );
INSERT INTO REP
VALUES
('15','Campos','Rafael','724 Vinca Dr.','Grove','CA','90092',23457.50,0.06);
INSERT INTO REP
VALUES
('30','Gradey','Megan','632 Liatris St.','Fullton','CA','90085',41317.00,0.08);
INSERT INTO REP
VALUES
('45','Tian','Hui','1785 Tyler Ave.','Northfield','CA','90098',27789.25,0.06);
INSERT INTO REP
VALUES
('60','Sefton','Janet','267 Oakley St.','Congaree','CA','90097',0.00,0.06);
INSERT INTO CUSTOMER
VALUES
('126','Toys Galore','28 Laketon St.','Fullton','CA','90085',1210.25,7500.00,'15');
INSERT INTO CUSTOMER
VALUES
('260','Brookings Direct','452 Columbus Dr.','Grove','CA','90092',575.00,10000.00,'30');
INSERT INTO CUSTOMER
VALUES
('334','The Everything Shop','342 Magee St.','Congaree','CA','90097',2345.75,7500.00,'45');
INSERT INTO CUSTOMER
VALUES
('386','Johnson''s Department Store','124 Main St.','Northfield','CA','90098',879.25,7500.00,'30');
INSERT INTO CUSTOMER
VALUES
('440','Grove Historical Museum Store','3456 Central Ave.','Fullton','CA','90085',345.00,5000.00,'45');
INSERT INTO CUSTOMER
VALUES
('502','Cards and More','167 Hale St.','Mesa','CA','90104',5025.75,5000.00,'15');
INSERT INTO CUSTOMER
VALUES
('586','Almondton General Store','3345 Devon Ave.','Almondton','CA','90125',3456.75,15000.00,'45');
INSERT INTO CUSTOMER
VALUES
('665','Cricket Gift Shop','372 Oxford St.','Grove','CA','90092',678.90,7500.00,'30');
INSERT INTO CUSTOMER
VALUES
('713','Cress Store','12 Rising Sun Ave.','Congaree','CA','90097',4234.60,10000.00,'15');
INSERT INTO CUSTOMER
VALUES
('796','Unique Gifts','786 Passmore St.','Northfield','CA','90098',124.75,7500.00,'45');
INSERT INTO CUSTOMER
VALUES
('824','Kline''s','945 Gilham St.','Mesa','CA','90104',2475.99,15000.00,'30');
INSERT INTO CUSTOMER
VALUES
('893','All Season Gifts','382 Wildwood Ave.','Fullton','CA','90085',935.75,7500.00,'15');
INSERT INTO ORDERS
VALUES
('51608','2015-10-12','126');
INSERT INTO ORDERS
VALUES
('51610','2015-10-12','334');
INSERT INTO ORDERS
VALUES
('51613','2015-10-13','386');
INSERT INTO ORDERS
VALUES
('51614','2015-10-13','260');
INSERT INTO ORDERS
VALUES
('51617','2015-10-15','586');
INSERT INTO ORDERS
VALUES
('51619','2015-10-15','126');
INSERT INTO ORDERS
VALUES
('51623','2015-10-15','586');
INSERT INTO ORDERS
VALUES
('51625','2015-10-16','796');
INSERT INTO ITEM
VALUES
('AH74','Patience',9.00,'GME','3',22.99);
INSERT INTO ITEM
VALUES
('BR23','Skittles',21.00,'GME','2',29.99);
INSERT INTO ITEM
VALUES
('CD33','Wood Block Set (48 piece)',36.00,'TOY','1',89.49);
INSERT INTO ITEM
VALUES
('DL51','Classic Railway Set',12.00,'TOY','3',107.95);
INSERT INTO ITEM
VALUES
('DR67','Giant Star Brain Teaser',24.00,'PZL','2',31.95);
INSERT INTO ITEM
VALUES
('DW23','Mancala',40.00,'GME','3',50.00);
INSERT INTO ITEM
VALUES
('FD11','Rocking Horse',8.00,'TOY','3',124.95);
INSERT INTO ITEM
VALUES
('FH24','Puzzle Gift Set',65.00,'PZL','1',38.95);
INSERT INTO ITEM
VALUES
('KA12','Cribbage Set',56.00,'GME','3',75.00);
INSERT INTO ITEM
VALUES
('KD34','Pentominoes Brain Teaser',60.00,'PZL','2',14.95);
INSERT INTO ITEM
VALUES
('KL78','Pick Up Sticks',110.00,'GME','1',10.95);
INSERT INTO ITEM
VALUES
('MT03','Zauberkasten Brain Teaser',45.00,'PZL','1',45.79);
INSERT INTO ITEM
VALUES
('NL89','Wood Block Set (62 piece)',32.00,'TOY','3',119.75);
INSERT INTO ITEM
VALUES
('TR40','Tic Tac Toe',75.00,'GME','2',13.99);
INSERT INTO ITEM
VALUES
('TW35','Fire Engine',30.00,'TOY','2',118.95);
INSERT INTO OrderLine
VALUES
('51608','CD33',5.00,86.99);
INSERT INTO OrderLine
VALUES
('51610','KL78',25.00,10.95);
INSERT INTO OrderLine
VALUES
('51610','TR40',10.00,13.99);
INSERT INTO OrderLine
VALUES
('51613','DL51',5.00,104.95);
INSERT INTO OrderLine
VALUES
('51614','FD11',1.00,124.95);
INSERT INTO OrderLine
VALUES
('51617','NL89',4.00,115.99);
INSERT INTO OrderLine
VALUES
('51617','TW35',3.00,116.95);
INSERT INTO OrderLine
VALUES
('51619','FD11',2.00,121.95);
INSERT INTO OrderLine
VALUES
('51623','DR67',5.00,29.95);
INSERT INTO OrderLine
VALUES
('51623','FH24',12.00,36.95);
INSERT INTO OrderLine
VALUES
('51623','KD34',10.00,13.10);
INSERT INTO OrderLine
VALUES
('51625','MT03',8.00,45.79);