BjjwH56z

1drop database if exists emc;
2create database emc;
3use emc;
4
5create table client_type (
6id			int(4) 			PRIMARY KEY,
7clientTypeName 		varchar(255) 	NOT NULL);
8
9create table client (
10id 			int(4) 			PRIMARY KEY,
11type 		int(4)			NOT NULL,
12name 		varchar(255)	NOT NULL,
13address		varchar(255)	NOT NULL,
14repName 	varchar(255)	NOT NULL,
15repContact 	int				NOT NULL UNIQUE,
16CONSTRAINT 	clientType_fk FOREIGN KEY (type) REFERENCES client_type(id));
17
18create table client_contact (
19name		varchar(255)	NOT NULL,
20contact 	int				NOT NULL UNIQUE,
21clientID 	int(4)			NOT NULL,
22email 		varchar(255)	NULL,
23CONSTRAINT 	clientID_fk FOREIGN KEY (clientID) REFERENCES client(id));
24
25create table acc_type (
26id 			int(4) 			PRIMARY KEY,
27name 		varchar(255)	NOT NULL);
28
29create table acc (
30id 			int(4) 			PRIMARY KEY,
31accType 	int(4)			NOT NULL,
32name 		varchar(255)	NOT NULL,
33maxOccupants int			NOT NULL,
34charge		smallint		NOT NULL,
35CONSTRAINT 	accType_fk FOREIGN KEY (accType) REFERENCES acc_type(id));
36
37create table advisor (
38id			int(4)			PRIMARY KEY,
39name		varchar(255)	NOT NULL,
40salary        decimal(13,2)   NOT NULL);
41
42create table booking (
43id 			int(4) 			PRIMARY KEY,
44client 		int(4)			NOT NULL,
45advisor		int(4)			NULL,
46bookingDate date			NOT NULL,
47startDate 	date			NOT NULL,
48endDate 	date			NOT NULL,
49totalPeople int				NOT NULL,
50acc 		int(4)			NOT NULL,
51catering 	varchar(255)	NOT NULL,
52CONSTRAINT 	client_fk FOREIGN KEY (client) REFERENCES client(id),
53CONSTRAINT 	acc_fk FOREIGN KEY (acc) REFERENCES acc(id),
54CONSTRAINT 	advisor_fk FOREIGN KEY (advisor) REFERENCES advisor(id),
55CONSTRAINT 	my_catering CHECK (catering = 'RESTAURANT' OR catering = 'SELF'));
56
57create table rating (
58bookingId 	int(4)			NOT NULL,
59rating		int(1)			NOT NULL,
60note		varchar(255)	NULL,
61CONSTRAINT bookingIdr_fk FOREIGN KEY (bookingId) REFERENCES booking(id));
62
63create table facilities (
64id			int(4)			PRIMARY KEY,
65name		varchar(255)	NOT NULL,
66charge		smallint		NOT NULL);
67
68create table outdoors (
69id			int(4)			PRIMARY KEY,
70name		varchar(255)	NOT NULL,
71charge 		smallint		NOT NULL);
72
73create table staff (
74id			int(4)			PRIMARY KEY,
75name		varchar(255)	NOT NULL,
76contract	varchar(255)	NOT NULL,
77salary	    decimal(13,2)   NOT NULL,
78supervisor	int(4)			NOT NULL,
79CONSTRAINT my_contract CHECK (contract = 'PART-TIME' OR contract = 'FULL-TIME'),
80CONSTRAINT supervisor_fk FOREIGN KEY (supervisor) REFERENCES outdoors(id));
81
82create table booking_facilities (
83bookingID	int(4)			NOT NULL,
84facilities 	int(4)			NOT NULL,
85date		date			NOT NULL,
86CONSTRAINT bookingfacID_fk	FOREIGN KEY (bookingID) REFERENCES booking(id),
87CONSTRAINT facilities_fk FOREIGN KEY (facilities) REFERENCES facilities(id));
88
89create table booking_outdoors (
90bookingID	int(4)			NOT NULL,
91outdoors 	int(4)			NOT NULL,
92date		date			NOT NULL,
93CONSTRAINT bookingoutID_fk FOREIGN KEY (bookingID) REFERENCES booking(id),
94CONSTRAINT outdoors_fk FOREIGN KEY (outdoors) REFERENCES outdoors(id));
95
96create table booking_snoopyLupiz (
97bookingID	int(4)			NOT NULL,
98singleroom	tinyint			NULL,
99doubleroom 	tinyint			NULL,
100kingsuite	tinyint			NULL, 
101luxurysuite	tinyint			NULL,
102CONSTRAINT bookingID_fk FOREIGN KEY (bookingID) REFERENCES booking(id));
103
104
105insert into client_type values (0001, 'Family');
106insert into client_type values (0002, 'Business');
107insert into client_type values (0003, 'School');
108insert into client_type values (0004, 'Youth Group');
109
110insert into client values (1001, 3, 'Maira\'s International School', 'Washington', 'Tyson', 179184289);
111insert into client values (1002, 1, 'Devon and Family', 'Springfield', 'Aurore', 189373252);
112insert into client values (1003, 3, 'Verlie\'s International School', 'Springfield', 'Suk', 145797438);
113insert into client values (1004, 4, 'Daniela Youth Group', 'Bristol', 'Nichelle', 122719731);
114insert into client values (1005, 3, 'Dakota\'s International School', 'Dover', 'Mathilde', 177414314);
115insert into client values (1006, 1, 'Karlene and Family', 'Oxford', 'Myung', 124888779);
116insert into client values (1007, 4, 'Lavina Youth Group', 'Oxford', 'Lynda', 115942338);
117insert into client values (1008, 2, 'Tiffiny and Co.', 'Lexington', 'Constance', 115169518);
118insert into client values (1009, 2, 'Alfonso and Co.', 'Springfield', 'Jovita', 166417485);
119insert into client values (1010, 1, 'Avery and Family', 'Washington', 'Jana', 138441668);
120insert into client values (1011, 1, 'Damon and Family', 'Hudson', 'Joycelyn', 158374862);
121insert into client values (1012, 4, 'Damaris Youth Group', 'Bristol', 'Lorene', 129897562);
122insert into client values (1013, 1, 'Gil and Family', 'Oakland', 'Jama', 161398593);
123insert into client values (1014, 4, 'Ashton Youth Group', 'Madison', 'Sharda', 145352146);
124insert into client values (1015, 3, 'Marcell\'s International School', 'Oxford', 'Ashlee', 199326331);
125insert into client values (1016, 2, 'Francesco and Co.', 'Auburn', 'Garth', 132431419);
126insert into client values (1017, 1, 'Tricia and Family', 'Manchester', 'Ka', 174244989);
127insert into client values (1018, 3, 'Larry\'s International School', 'Washington', 'Wendie', 111884618);
128insert into client values (1019, 4, 'Clifton Youth Group', 'Springfield', 'Werner', 149512627);
129insert into client values (1020, 2, 'Serena and Co.', 'Manchester', 'Carlos', 141724562);
130insert into client values (1021, 4, 'Howard Youth Group', 'Burlington', 'Toshia', 157678296);
131insert into client values (1022, 1, 'Lourie and Family', 'Jackson', 'Bianca', 162998631);
132insert into client values (1023, 3, 'Yelena\'s International School', 'Oxford', 'Barb', 117572587);
133insert into client values (1024, 3, 'Krysta\'s International School', 'Washington', 'Cristine', 162822896);
134insert into client values (1025, 4, 'Dawn Youth Group', 'Bristol', 'Ashlea', 146453655);
135insert into client values (1026, 1, 'Magen and Family', 'Springfield', 'Thad', 136565338);
136insert into client values (1027, 2, 'Ardelia and Co.', 'Oxford', 'Omer', 158513664);
137insert into client values (1028, 3, 'Hiram\'s International School', 'Georgetown', 'Foster', 143265599);
138insert into client values (1029, 3, 'Rhea\'s International School', 'Auburn', 'Sharen', 121272177);
139insert into client values (1030, 4, 'Millard Youth Group', 'Madison', 'Rolanda', 129812855);
140
141insert into client_contact values ('Cinthia', 171684565, 1003, NULL);
142insert into client_contact values ('Sadie', 152444456, 1029, 'Sadie@gmail.com');
143insert into client_contact values ('Roger', 187659462, 1006, 'Roger@gmail.com');
144insert into client_contact values ('Kattie', 182222762, 1030, NULL);
145insert into client_contact values ('Lila', 185587429, 1027, NULL);
146insert into client_contact values ('Brice', 191253153, 1026, NULL);
147insert into client_contact values ('Rosamond', 146625519, 1020, 'Rosamond@gmail.com');
148insert into client_contact values ('Frank', 122615294, 1027, NULL);
149insert into client_contact values ('Tracy', 112837337, 1029, NULL);
150insert into client_contact values ('Shenika', 197844692, 1013, NULL);
151insert into client_contact values ('Criselda', 116755121, 1013, 'Criselda@gmail.com');
152insert into client_contact values ('Myron', 184451318, 1028, NULL);
153insert into client_contact values ('Tesha', 184689376, 1025, NULL);
154insert into client_contact values ('Virginia', 191517761, 1003, 'Virginia@gmail.com');
155insert into client_contact values ('Donald', 173973382, 1017, NULL);
156insert into client_contact values ('Salvador', 157119578, 1001, 'Salvador@gmail.com');
157insert into client_contact values ('Twanna', 182114668, 1010, NULL);
158insert into client_contact values ('Alycia', 145814292, 1006, NULL);
159insert into client_contact values ('Glory', 126996977, 1028, 'Glory@gmail.com');
160insert into client_contact values ('Hermila', 127735291, 1029, 'Hermila@gmail.com');
161insert into client_contact values ('Jan', 164172963, 1018, NULL);
162insert into client_contact values ('Neil', 182768369, 1020, NULL);
163insert into client_contact values ('Oralee', 132325859, 1024, 'Oralee@gmail.com');
164insert into client_contact values ('Rina', 186357158, 1005, NULL);
165insert into client_contact values ('Ingeborg', 177848639, 1026, 'Ingeborg@gmail.com');
166insert into client_contact values ('Kathrin', 145384391, 1006, 'Kathrin@gmail.com');
167insert into client_contact values ('Kerry', 112627879, 1027, 'Kerry@gmail.com');
168insert into client_contact values ('Marylou', 181319913, 1020, NULL);
169insert into client_contact values ('Jackie', 184521224, 1030, 'Jackie@gmail.com');
170insert into client_contact values ('Florencio', 127816595, 1006, 'Florencio@gmail.com');
171
172insert into acc_type values (0001, 'Hotel');
173insert into acc_type values (0002, 'Hostel');
174insert into acc_type values (0003, 'Camping Ground');
175
176insert into acc values (2001, 1, 'Snoopy Lupiz'   , 230, 100);
177insert into acc values (2002, 2, 'Laquanda\'s Hostel'   , 48, 70);
178insert into acc values (2003, 2, 'Nathanial\'s Hostel'   , 40, 70);
179insert into acc values (2004, 3, 'Joy\'s Camping Ground'   , 42, 40);
180insert into acc values (2005, 3, 'Lanie\'s Camping Ground'   , 39, 25);
181insert into acc values (2006, 2, 'Fletcher\'s Hostel'   , 46, 60);
182insert into acc values (2007, 2, 'Lesley\'s Hostel'   , 42, 50);
183insert into acc values (2008, 3, 'Berna\'s Camping Ground'   , 30, 40);
184insert into acc values (2009, 2, 'Terisa\'s Hostel'   , 39, 45);
185insert into acc values (2010, 3, 'Brigida\'s Camping Ground'   , 41, 30);
186insert into acc values (2011, 2, 'Viviana\'s Hostel'   , 39, 65);
187insert into acc values (2012, 2, 'Kecia\'s Hostel'   , 35, 85);
188insert into acc values (2013, 3, 'Ivan\'s Camping Ground'   , 44, 35);
189insert into acc values (2014, 3, 'Verdell\'s Camping Ground'   , 34, 20);
190insert into acc values (2015, 3, 'Nicola\'s Camping Ground'   , 34, 25);
191insert into acc values (2016, 3, 'Marie\'s Camping Ground'   , 43, 20);
192insert into acc values (2017, 3, 'Sherrill\'s Camping Ground'   , 49, 25);
193insert into acc values (2018, 2, 'Hyo\'s Hostel'   , 39, 70);
194insert into acc values (2019, 3, 'Madlyn\'s Camping Ground'   , 31, 25);
195insert into acc values (2020, 2, 'Veronica\'s Hostel'   , 36, 85);
196insert into acc values (2021, 3, 'Luvenia\'s Camping Ground'   , 42, 25);
197insert into acc values (2022, 2, 'Elijah\'s Hostel'   , 43, 55);
198insert into acc values (2023, 3, 'Jolanda\'s Camping Ground'   , 48, 30);
199insert into acc values (2024, 3, 'Renaldo\'s Camping Ground'   , 40, 30);
200insert into acc values (2025, 2, 'Vella\'s Hostel'   , 31, 65);
201insert into acc values (2026, 2, 'Edith\'s Hostel'   , 47, 60);
202insert into acc values (2027, 3, 'Millicent\'s Camping Ground'   , 39, 50);
203insert into acc values (2028, 3, 'Dotty\'s Camping Ground'   , 40, 35);
204insert into acc values (2029, 2, 'Samella\'s Hostel'   , 45, 60);
205insert into acc values (2030, 3, 'Darryl\'s Camping Ground'   , 31, 15);
206
207insert into advisor values (8001, 'Gordon', 2000);
208insert into advisor values (8002, 'Dottie', 2000);
209insert into advisor values (8003, 'Thomasine', 2000);
210insert into advisor values (8004, 'Brittany', 2000);
211insert into advisor values (8005, 'Nell', 2500);
212insert into advisor values (8006, 'Nannette', 2750);
213insert into advisor values (8007, 'Shery', 2500);
214insert into advisor values (8008, 'Randell', 2500);
215insert into advisor values (8009, 'Halley', 2500);
216insert into advisor values (8010, 'Martina', 2500);
217insert into advisor values (8011, 'Alvina', 3000);
218insert into advisor values (8012, 'Ester', 2750);
219insert into advisor values (8013, 'Letisha', 2000);
220insert into advisor values (8014, 'Brande', 2500);
221insert into advisor values (8015, 'Louanne', 2750);
222insert into advisor values (8016, 'Lawerence', 2000);
223insert into advisor values (8017, 'Camellia', 2000);
224insert into advisor values (8018, 'Bart', 2000);
225insert into advisor values (8019, 'Edith', 2000);
226insert into advisor values (8020, 'Adella', 3000);
227insert into advisor values (8021, 'Caroyln', 2250);
228insert into advisor values (8022, 'Mike', 2500);
229insert into advisor values (8023, 'Senaida', 2500);
230insert into advisor values (8024, 'Tania', 2500);
231insert into advisor values (8025, 'Odelia', 2750);
232insert into advisor values (8026, 'Melonie', 3000);
233insert into advisor values (8027, 'Herlinda', 2500);
234insert into advisor values (8028, 'Ellan', 2750);
235insert into advisor values (8029, 'Forrest', 2500);
236insert into advisor values (8030, 'Tyron', 2000);
237
238select bookingID from booking_outdoors where outdoors = 6001;
239
240insert into booking values (3001, 1001, 8001, '2013-2-1', '2013-3-2', '2013-3-4', 18, 2014, 'SELF');
241insert into booking values (3002, 1014, 8001, '2013-3-7', '2013-3-12', '2013-3-14', 25, 2010, 'RESTAURANT');
242insert into booking values (3003, 1007, 8001, '2013-3-17', '2013-3-20', '2013-3-24', 23, 2001, 'RESTAURANT');
243insert into booking values (3004, 1030, NULL, '2013-4-27', '2013-6-1', '2013-6-4', 15, 2008, 'RESTAURANT');
244insert into booking values (3005, 1016, 8003, '2013-6-6', '2013-6-9', '2013-6-13', 27, 2015, 'RESTAURANT');
245insert into booking values (3006, 1011, 8004, '2013-7-14', '2013-7-16', '2013-7-20', 21, 2015, 'RESTAURANT');
246insert into booking values (3007, 1030, 8005, '2013-8-22', '2013-8-27', '2013-9-1', 21, 2006, 'SELF');
247insert into booking values (3008, 1008, 8006, '2013-9-4', '2013-10-8', '2013-10-12', 20, 2003, 'SELF');
248insert into booking values (3009, 1013, NULL, '2013-10-13', '2013-10-16', '2013-10-20', 22, 2017, 'RESTAURANT');
249insert into booking values (3010, 1018, 8007, '2013-10-20', '2013-11-24', '2013-11-26', 15, 2030, 'RESTAURANT');
250insert into booking values (3011, 1003, 8007, '2014-1-28', '2014-3-1', '2014-3-4', 30, 2001, 'SELF');
251insert into booking values (3012, 1004, 8008, '2014-4-4', '2014-5-9', '2014-5-13', 21, 2010, 'SELF');
252insert into booking values (3013, 1028, 8009, '2014-5-16', '2014-5-21', '2014-5-24', 16, 2004, 'RESTAURANT');
253insert into booking values (3014, 1004, 8010, '2014-6-27', '2014-7-1', '2014-7-4', 21, 2016, 'SELF');
254insert into booking values (3015, 1007, 8010, '2014-7-5', '2014-7-8', '2014-7-12', 17, 2018, 'RESTAURANT');
255insert into booking values (3016, 1027, 8011, '2014-8-12', '2014-8-13', '2014-8-17', 18, 2001, 'RESTAURANT');
256insert into booking values (3017, 1002, NULL, '2014-9-17', '2014-10-21', '2014-10-23', 16, 2006, 'SELF');
257insert into booking values (3018, 1012, NULL, '2014-10-26', '2014-10-27', '2014-11-1', 19, 2006, 'RESTAURANT');
258insert into booking values (3019, 1025, 8012, '2015-1-2', '2015-1-5', '2015-1-8', 25, 2003, 'RESTAURANT');
259insert into booking values (3020, 1006, 8013, '2015-2-9', '2015-2-13', '2015-2-17', 16, 2020, 'SELF');
260insert into booking values (3021, 1010, 8013, '2015-3-17', '2015-3-22', '2015-3-24', 27, 2006, 'SELF');
261insert into booking values (3022, 1002, 8015, '2015-3-25', '2015-3-26', '2015-3-29', 15, 2024, 'RESTAURANT');
262insert into booking values (3023, 1018, 8016,'2015-4-1', '2015-4-4', '2015-4-6', 16, 2014, 'SELF');
263insert into booking values (3024, 1024, 8017, '2015-5-9', '2015-6-9', '2015-6-13', 16, 2016, 'SELF');
264insert into booking values (3025, 1017, 8018,'2015-6-13', '2015-7-16', '2015-7-19', 29, 2023, 'SELF');
265insert into booking values (3026, 1023, 8018, '2015-7-21', '2015-7-25', '2015-7-29', 26, 2025, 'RESTAURANT');
266insert into booking values (3027, 1030, 8019, '2015-9-1', '2015-9-3', '2015-9-5', 23, 2003, 'RESTAURANT');
267insert into booking values (3028, 1009, 8020, '2015-10-6', '2015-11-10', '2015-11-12', 19, 2005, 'SELF');
268insert into booking values (3029, 1001, NULL, '2015-11-14', '2015-11-14', '2015-11-18', 19, 2015, 'RESTAURANT');
269insert into booking values (3030, 1030, 8021,'2016-1-20', '2016-2-24', '2016-2-27', 29, 2005, 'RESTAURANT');
270insert into booking values (3031, 1005, 8021, '2016-2-29', '2016-3-1', '2016-3-3', 26, 2026, 'SELF');
271insert into booking values (3032, 1019, 8022, '2016-3-4', '2016-3-9', '2016-3-13', 17, 2019, 'SELF');
272insert into booking values (3033, 1006, 8023, '2016-4-13', '2016-5-13', '2016-5-16', 20, 2028, 'RESTAURANT');
273insert into booking values (3034, 1029, 8024, '2016-5-18', '2016-6-19', '2016-6-22', 30, 2018, 'RESTAURANT');
274insert into booking values (3035, 1005, NULL, '2016-6-25', '2016-7-25', '2016-7-29', 15, 2027, 'RESTAURANT');
275insert into booking values (3036, 1020, 8025, '2016-8-1', '2016-9-4', '2016-9-8', 20, 2028, 'SELF');
276insert into booking values (3037, 1021, 8026, '2016-9-9', '2016-10-12', '2016-10-14', 26, 2023, 'SELF');
277insert into booking values (3038, 1023, 8026, '2016-10-17', '2016-11-18', '2016-11-20', 30, 2024, 'SELF');
278insert into booking values (3039, 1003, 8027, '2016-11-20', '2016-11-25', '2016-11-27', 19, 2025, 'SELF');
279insert into booking values (3040, 1026, 8028, '2016-11-28', '2017-1-1', '2017-1-5', 29, 2013, 'SELF');
280insert into booking values (3041, 1009, NULL, '2017-2-6', '2017-2-6', '2017-2-8', 19, 2014, 'RESTAURANT');
281insert into booking values (3042, 1004, 8029, '2017-2-10', '2017-2-12', '2017-2-15', 27, 2005, 'SELF');
282insert into booking values (3043, 1004, 8030, '2017-3-18', '2017-3-20', '2017-3-24', 22, 2007, 'RESTAURANT');
283insert into booking values (3044, 1016, 8030, '2017-3-25', '2017-3-29', '2017-4-1', 21, 2012, 'SELF');
284insert into booking values (3045, 1011, 8001, '2017-4-4', '2017-4-8', '2017-4-12', 25, 2002, 'RESTAURANT');
285insert into booking values (3046, 1009, 8002, '2017-4-13', '2017-5-18', '2017-5-22', 17, 2025, 'RESTAURANT');
286insert into booking values (3047, 1015, 8002, '2017-6-25', '2017-7-1', '2017-7-3', 24, 2002, 'RESTAURANT');
287insert into booking values (3048, 1022, 8003, '2017-8-6', '2017-8-11', '2017-8-13', 29, 2012, 'SELF');
288insert into booking values (3049, 1014, 8004, '2017-8-15', '2017-8-20', '2017-8-23', 16, 2008, 'RESTAURANT');
289insert into booking values (3050, 1006, NULL, '2017-9-26', '2017-9-27', '2017-9-29', 22, 2018, 'RESTAURANT');
290	
291insert into rating values(3001, 4, "");
292insert into rating values(3002, 5, "");
293insert into rating values(3003, 4, "Hotel was extremely nice, will definitely visit again!");
294insert into rating values(3004, 5, "");
295insert into rating values(3005, 1, "Staff was very rude");
296insert into rating values(3006, 3, "");
297insert into rating values(3007, 4, "Conference Hall very spacious and well equipped, very glad we chose EMC.");
298insert into rating values(3008, 3, "");
299insert into rating values(3009, 4, NULL);
300insert into rating values(3010, 1, "Computers very slow and dated, please upgrade!");
301insert into rating values(3011, 4, NULL);
302insert into rating values(3012, 2, NULL);
303insert into rating values(3013, 3, "");
304insert into rating values(3014, 4, "");
305insert into rating values(3015, 4, NULL);
306insert into rating values(3016, 5, "Paintball was very fun, even got to shoot the boss!");
307insert into rating values(3017, 4, NULL);
308insert into rating values(3018, 3, "Paintball was fun, but pricing was a little bit expensive.");
309insert into rating values(3019, 4, "");
310insert into rating values(3020, 4, NULL);
311insert into rating values(3021, 3, "");
312insert into rating values(3022, 1, NULL);
313insert into rating values(3023, 2, "Computers in lab 2 very unresponsive and slow!");
314insert into rating values(3024, 5, "");
315insert into rating values(3025, 4, NULL);
316insert into rating values(3026, 2, NULL);
317insert into rating values(3027, 5, NULL);
318insert into rating values(3028, 4, NULL);
319insert into rating values(3029, 5, "");
320insert into rating values(3030, 3, NULL);
321insert into rating values(3031, 4, NULL);
322insert into rating values(3032, 2, "We stayed at Dotty's camping ground and it was very unsanitary and dirty.");
323insert into rating values(3033, 3, NULL);
324insert into rating values(3034, 3, NULL);
325insert into rating values(3035, 2, NULL);
326insert into rating values(3036, 1, "Camping ground very dirty, even saw multiple rats crawling about!");
327insert into rating values(3037, 2, NULL);
328insert into rating values(3038, 3, "");
329insert into rating values(3039, 5, "");
330insert into rating values(3040, 5, "");
331insert into rating values(3041, 4, NULL);
332insert into rating values(3042, 4, "");
333insert into rating values(3043, 4, NULL);
334insert into rating values(3044, 3, "");
335insert into rating values(3045, 3, NULL);
336insert into rating values(3046, 3, NULL);
337insert into rating values(3047, 5, NULL);
338insert into rating values(3048, 2, "");
339insert into rating values(3049, 2, NULL);
340insert into rating values(3050, 4, "");   
341
342insert into facilities values (5001, 'Board Room A', 500);
343insert into facilities values (5002, 'Board Room B', 350);
344insert into facilities values (5003, 'Conference Hall', 800);
345insert into facilities values (5004, 'Computing Lab 1', 200);
346insert into facilities values (5005, 'Computing Lab 2', 150);
347insert into facilities values (5006, 'Computing Lab 3', 150);
348insert into facilities values (5007, 'Multimedia Equipment', 70);
349
350insert into outdoors values (6001, 'Wall-climbing', 100);
351insert into outdoors values (6002, 'Paintball', 60);
352insert into outdoors values (6003, 'Canoeing', 60);
353insert into outdoors values (6004, 'Swimming', 60);
354insert into outdoors values (6005, 'Basketball', 40);
355insert into outdoors values (6006, 'Football', 40);
356
357insert into staff values (7001, 'Madaline', 'FULL-TIME', 3500, 6001);
358insert into staff values (7002, 'Daine', 'FULL-TIME', 3500, 6002);
359insert into staff values (7003, 'Ardelia', 'PART-TIME', 4000, 6003);
360insert into staff values (7004, 'Kellye', 'FULL-TIME', 3750, 6004);
361insert into staff values (7005, 'Noelia', 'FULL-TIME', 3500, 6005);
362insert into staff values (7006, 'Alexandria', 'PART-TIME', 3750, 6006);
363insert into staff values (7007, 'Anabel', 'PART-TIME', 3750, 6003);
364insert into staff values (7008, 'Breanne', 'FULL-TIME', 3500, 6004);
365insert into staff values (7009, 'Roselia', 'PART-TIME', 3500, 6002);
366insert into staff values (7010, 'Ronald', 'PART-TIME', 4000, 6002);
367
368insert into booking_facilities values (3001, 5003, '2013-3-3');
369insert into booking_facilities values (3002, 5004, '2013-3-13');
370insert into booking_facilities values (3003, 5003, '2013-3-21');
371insert into booking_facilities values (3004, 5002, '2013-6-3');
372insert into booking_facilities values (3005, 5007, '2013-6-10');
373insert into booking_facilities values (3006, 5001, '2013-7-17');
374insert into booking_facilities values (3007, 5003, '2013-8-27');
375insert into booking_facilities values (3008, 5004, '2013-10-9');
376insert into booking_facilities values (3009, 5006, '2013-10-16');
377insert into booking_facilities values (3010, 5005, '2013-11-25');
378insert into booking_facilities values (3011, 5001, '2014-3-2');
379insert into booking_facilities values (3012, 5003, '2014-5-9');
380insert into booking_facilities values (3013, 5002, '2014-5-22');
381insert into booking_facilities values (3014, 5006, '2014-7-3');
382insert into booking_facilities values (3015, 5001, '2014-7-8');
383insert into booking_facilities values (3016, 5002, '2014-8-14');
384insert into booking_facilities values (3017, 5003, '2014-10-21');
385insert into booking_facilities values (3018, 5004, '2014-10-29');
386insert into booking_facilities values (3019, 5004, '2015-1-6');
387insert into booking_facilities values (3020, 5002, '2015-2-13');
388insert into booking_facilities values (3021, 5007, '2015-3-22');
389insert into booking_facilities values (3022, 5001, '2015-3-27');
390insert into booking_facilities values (3023, 5005, '2015-4-5');
391insert into booking_facilities values (3024, 5004, '2015-6-9');
392insert into booking_facilities values (3025, 5003, '2015-7-17');
393insert into booking_facilities values (3026, 5003, '2015-7-26');
394insert into booking_facilities values (3027, 5007, '2015-9-4');
395insert into booking_facilities values (3028, 5007, '2015-11-11');
396insert into booking_facilities values (3029, 5002, '2015-11-14');
397insert into booking_facilities values (3030, 5007, '2016-2-25');
398
399insert into booking_outdoors values (3001, 6006, '2013-3-3');
400insert into booking_outdoors values (3002, 6006, '2013-3-12');
401insert into booking_outdoors values (3003, 6004, '2013-3-21');
402insert into booking_outdoors values (3003, 6003, '2013-3-22');
403insert into booking_outdoors values (3005, 6005, '2013-6-9');
404insert into booking_outdoors values (3006, 6005, '2013-7-17');
405insert into booking_outdoors values (3007, 6005, '2013-8-28');
406insert into booking_outdoors values (3007, 6005, '2013-8-28');
407insert into booking_outdoors values (3008, 6003, '2013-10-8');
408insert into booking_outdoors values (3010, 6005, '2013-11-25');
409insert into booking_outdoors values (3012, 6006, '2014-5-10');
410insert into booking_outdoors values (3014, 6005, '2014-7-2');
411insert into booking_outdoors values (3016, 6005, '2014-8-14');
412insert into booking_outdoors values (3016, 6001, '2014-8-15');
413insert into booking_outdoors values (3018, 6001, '2014-10-17');
414insert into booking_outdoors values (3019, 6004, '2015-1-6');
415insert into booking_outdoors values (3020, 6004, '2015-2-14');
416insert into booking_outdoors values (3021, 6004, '2015-3-23');
417insert into booking_outdoors values (3023, 6003, '2015-4-4');
418insert into booking_outdoors values (3023, 6004, '2015-4-5');
419insert into booking_outdoors values (3025, 6003, '2015-7-17');
420insert into booking_outdoors values (3025, 6004, '2015-7-17');
421insert into booking_outdoors values (3027, 6006, '2015-9-5');
422insert into booking_outdoors values (3029, 6002, '2015-11-15');
423insert into booking_outdoors values (3029, 6002, '2015-11-16');
424insert into booking_outdoors values (3030, 6001, '2016-2-24');
425insert into booking_outdoors values (3031, 6006, '2016-3-2');
426insert into booking_outdoors values (3032, 6002, '2016-3-10');
427insert into booking_outdoors values (3034, 6004, '2016-6-10');
428insert into booking_outdoors values (3036, 6004, '2016-9-5');
429
430insert into booking_snoopyLupiz values (3003, 3, 10, 0, 0);
431insert into booking_snoopyLupiz values (3011, 25, 0, 1, 1);
432insert into booking_snoopyLupiz values (3016, 0, 9, 0, 0);
433
434-- ///////////////////////////////////////////////////////////////////////////
435
436-- make sure to invoke procedure definition queries in procedure2.sql before calling these procedures
437
438call mostServiceTransaction(3);				-- 1 is FAMILY 	| 2 is BUSINESS		| 3 is SCHOOL 			| 4 is YOUTH GROUP
439call getClientList();
440call getAccTotal(2);						-- 1 is HOTEL 	| 2 is HOSTEL/DORM 	| 3 is CAMPING GROUND
441call increaseFacilities(0.05);
442
443call staffBookingMade();
444call maxSales;
445
446
447-- check for rating in (1, 2) and retrieve contact details to ask about experience and possibly provide compensation
448-- or print out all ratings (and contact details and comments) and sort by rating
449
450-- look at which facilities/accommodation/outdoors activities generated the most revenue and possibly add more instances of these
451-- retrieve contact details of clients and send vouchers/promotional news/marketing