API tools faq
paste
Advertisement
Guest User

Untitled

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