API tools faq
paste
Advertisement
Guest User

sql(7nov)

a guest
Nov 7th, 2018
304
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 26.05 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.  
  41. create table booking (
  42. id          int(4)          PRIMARY KEY,
  43. client      int(4)          NOT NULL,
  44. advisor     int(4)          NULL,
  45. bookingDate date            NOT NULL,
  46. startDate   date            NOT NULL,
  47. endDate     date            NOT NULL,
  48. totalPeople int             NOT NULL,
  49. acc         int(4)          NOT NULL,
  50. catering    varchar(255)    NOT NULL,
  51. CONSTRAINT  client_fk FOREIGN KEY (client) REFERENCES client(id),
  52. CONSTRAINT  acc_fk FOREIGN KEY (acc) REFERENCES acc(id),
  53. CONSTRAINT  advisor_fk FOREIGN KEY (advisor) REFERENCES advisor(id),
  54. CONSTRAINT  my_catering CHECK (catering = 'RESTAURANT' OR catering = 'SELF'));
  55.  
  56. create table rating (
  57. bookingId   int(4)          NOT NULL,
  58. rating      int(1)          NOT NULL,
  59. note        varchar(255)    NULL,
  60. CONSTRAINT bookingIdr_fk FOREIGN KEY (bookingId) REFERENCES booking(id));
  61.  
  62. create table facilities (
  63. id          int(4)          PRIMARY KEY,
  64. name        varchar(255)    NOT NULL,
  65. charge      smallint        NOT NULL);
  66.  
  67. create table outdoors (
  68. id          int(4)          PRIMARY KEY,
  69. name        varchar(255)    NOT NULL,
  70. charge      smallint        NOT NULL);
  71.  
  72. create table staff (
  73. id          int(4)          PRIMARY KEY,
  74. name        varchar(255)    NOT NULL,
  75. contract    varchar(255)    NOT NULL,
  76. supervisor  int(4)          NOT NULL,
  77. wages       smallint        NOT NULL,
  78. CONSTRAINT my_contract CHECK (contract = 'PART-TIME' OR contract = 'FULL-TIME'),
  79. CONSTRAINT supervisor_fk FOREIGN KEY (supervisor) REFERENCES outdoors(id));
  80.  
  81. create table booking_facilities (
  82. bookingID   int(4)          NOT NULL,
  83. facilities  int(4)          NOT NULL,
  84. date        date            NOT NULL,
  85. CONSTRAINT bookingfacID_fk  FOREIGN KEY (bookingID) REFERENCES booking(id),
  86. CONSTRAINT facilities_fk FOREIGN KEY (facilities) REFERENCES facilities(id));
  87.  
  88. create table booking_outdoors (
  89. bookingID   int(4)          NOT NULL,
  90. outdoors    int(4)          NOT NULL,
  91. date        date            NOT NULL,
  92. CONSTRAINT bookingoutID_fk FOREIGN KEY (bookingID) REFERENCES booking(id),
  93. CONSTRAINT outdoors_fk FOREIGN KEY (outdoors) REFERENCES outdoors(id));
  94.  
  95. create table booking_snoopyLupiz (
  96. bookingID   int(4)          NOT NULL,
  97. singleroom  tinyint         NULL,
  98. doubleroom  tinyint         NULL,
  99. kingsuite   tinyint         NULL,
  100. luxurysuite tinyint         NULL,
  101. CONSTRAINT bookingID_fk FOREIGN KEY (bookingID) REFERENCES booking(id));
  102.  
  103. -- population databse with data
  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');
  208. insert into advisor values (8002, 'Dottie');
  209. insert into advisor values (8003, 'Thomasine');
  210. insert into advisor values (8004, 'Brittany');
  211. insert into advisor values (8005, 'Nell');
  212. insert into advisor values (8006, 'Nannette');
  213. insert into advisor values (8007, 'Shery');
  214. insert into advisor values (8008, 'Randell');
  215. insert into advisor values (8009, 'Halley');
  216. insert into advisor values (8010, 'Martina');
  217. insert into advisor values (8011, 'Alvina');
  218. insert into advisor values (8012, 'Ester');
  219. insert into advisor values (8013, 'Letisha');
  220. insert into advisor values (8014, 'Brande');
  221. insert into advisor values (8015, 'Louanne');
  222. insert into advisor values (8016, 'Lawerence');
  223. insert into advisor values (8017, 'Camellia');
  224. insert into advisor values (8018, 'Bart');
  225. insert into advisor values (8019, 'Edith');
  226. insert into advisor values (8020, 'Adella');
  227. insert into advisor values (8021, 'Caroyln');
  228. insert into advisor values (8022, 'Mike');
  229. insert into advisor values (8023, 'Senaida');
  230. insert into advisor values (8024, 'Tania');
  231. insert into advisor values (8025, 'Odelia');
  232. insert into advisor values (8026, 'Melonie');
  233. insert into advisor values (8027, 'Herlinda');
  234. insert into advisor values (8028, 'Ellan');
  235. insert into advisor values (8029, 'Forrest');
  236. insert into advisor values (8030, 'Tyron');
  237.  
  238. insert into booking values (3001, 1001, 8001, '2013-2-1', '2013-3-2', '2013-3-4', 18, 2014, 'SELF');
  239. insert into booking values (3002, 1014, 8001, '2013-3-7', '2013-3-12', '2013-3-14', 25, 2010, 'RESTAURANT');
  240. insert into booking values (3003, 1007, 8001, '2013-3-17', '2013-3-20', '2013-3-24', 23, 2001, 'RESTAURANT');
  241. insert into booking values (3004, 1030, NULL, '2013-4-27', '2013-6-1', '2013-6-4', 15, 2008, 'RESTAURANT');
  242. insert into booking values (3005, 1016, 8003, '2013-6-6', '2013-6-9', '2013-6-13', 27, 2015, 'RESTAURANT');
  243. insert into booking values (3006, 1011, 8004, '2013-7-14', '2013-7-16', '2013-7-20', 21, 2015, 'RESTAURANT');
  244. insert into booking values (3007, 1030, 8005, '2013-8-22', '2013-8-27', '2013-9-1', 21, 2006, 'SELF');
  245. insert into booking values (3008, 1008, 8006, '2013-9-4', '2013-10-8', '2013-10-12', 20, 2003, 'SELF');
  246. insert into booking values (3009, 1013, NULL, '2013-10-13', '2013-10-16', '2013-10-20', 22, 2017, 'RESTAURANT');
  247. insert into booking values (3010, 1018, 8007, '2013-10-20', '2013-11-24', '2013-11-26', 15, 2030, 'RESTAURANT');
  248. insert into booking values (3011, 1003, 8007, '2014-1-28', '2014-3-1', '2014-3-4', 30, 2001, 'SELF');
  249. insert into booking values (3012, 1004, 8008, '2014-4-4', '2014-5-9', '2014-5-13', 21, 2010, 'SELF');
  250. insert into booking values (3013, 1028, 8009, '2014-5-16', '2014-5-21', '2014-5-24', 16, 2004, 'RESTAURANT');
  251. insert into booking values (3014, 1004, 8010, '2014-6-27', '2014-7-1', '2014-7-4', 21, 2016, 'SELF');
  252. insert into booking values (3015, 1007, 8010, '2014-7-5', '2014-7-8', '2014-7-12', 17, 2018, 'RESTAURANT');
  253. insert into booking values (3016, 1027, 8011, '2014-8-12', '2014-8-13', '2014-8-17', 18, 2001, 'RESTAURANT');
  254. insert into booking values (3017, 1002, NULL, '2014-9-17', '2014-10-21', '2014-10-23', 16, 2006, 'SELF');
  255. insert into booking values (3018, 1012, NULL, '2014-10-26', '2014-10-27', '2014-11-1', 19, 2006, 'RESTAURANT');
  256. insert into booking values (3019, 1025, 8012, '2015-1-2', '2015-1-5', '2015-1-8', 25, 2003, 'RESTAURANT');
  257. insert into booking values (3020, 1006, 8013, '2015-2-9', '2015-2-13', '2015-2-17', 16, 2020, 'SELF');
  258. insert into booking values (3021, 1010, 8013, '2015-3-17', '2015-3-22', '2015-3-24', 27, 2006, 'SELF');
  259. insert into booking values (3022, 1002, 8015, '2015-3-25', '2015-3-26', '2015-3-29', 15, 2024, 'RESTAURANT');
  260. insert into booking values (3023, 1018, 8016,'2015-4-1', '2015-4-4', '2015-4-6', 16, 2014, 'SELF');
  261. insert into booking values (3024, 1024, 8017, '2015-5-9', '2015-6-9', '2015-6-13', 16, 2016, 'SELF');
  262. insert into booking values (3025, 1017, 8018,'2015-6-13', '2015-7-16', '2015-7-19', 29, 2023, 'SELF');
  263. insert into booking values (3026, 1023, 8018, '2015-7-21', '2015-7-25', '2015-7-29', 26, 2025, 'RESTAURANT');
  264. insert into booking values (3027, 1030, 8019, '2015-9-1', '2015-9-3', '2015-9-5', 23, 2003, 'RESTAURANT');
  265. insert into booking values (3028, 1009, 8020, '2015-10-6', '2015-11-10', '2015-11-12', 19, 2005, 'SELF');
  266. insert into booking values (3029, 1001, NULL, '2015-11-14', '2015-11-14', '2015-11-18', 19, 2015, 'RESTAURANT');
  267. insert into booking values (3030, 1030, 8021,'2016-1-20', '2016-2-24', '2016-2-27', 29, 2005, 'RESTAURANT');
  268. insert into booking values (3031, 1005, 8021, '2016-2-29', '2016-3-1', '2016-3-3', 26, 2026, 'SELF');
  269. insert into booking values (3032, 1019, 8022, '2016-3-4', '2016-3-9', '2016-3-13', 17, 2019, 'SELF');
  270. insert into booking values (3033, 1006, 8023, '2016-4-13', '2016-5-13', '2016-5-16', 20, 2028, 'RESTAURANT');
  271. insert into booking values (3034, 1029, 8024, '2016-5-18', '2016-6-19', '2016-6-22', 30, 2018, 'RESTAURANT');
  272. insert into booking values (3035, 1005, NULL, '2016-6-25', '2016-7-25', '2016-7-29', 15, 2027, 'RESTAURANT');
  273. insert into booking values (3036, 1020, 8025, '2016-8-1', '2016-9-4', '2016-9-8', 20, 2028, 'SELF');
  274. insert into booking values (3037, 1021, 8026, '2016-9-9', '2016-10-12', '2016-10-14', 26, 2023, 'SELF');
  275. insert into booking values (3038, 1023, 8026, '2016-10-17', '2016-11-18', '2016-11-20', 30, 2024, 'SELF');
  276. insert into booking values (3039, 1003, 8027, '2016-11-20', '2016-11-25', '2016-11-27', 19, 2025, 'SELF');
  277. insert into booking values (3040, 1026, 8028, '2016-11-28', '2017-1-1', '2017-1-5', 29, 2013, 'SELF');
  278. insert into booking values (3041, 1009, NULL, '2017-2-6', '2017-2-6', '2017-2-8', 19, 2014, 'RESTAURANT');
  279. insert into booking values (3042, 1004, 8029, '2017-2-10', '2017-2-12', '2017-2-15', 27, 2005, 'SELF');
  280. insert into booking values (3043, 1004, 8030, '2017-3-18', '2017-3-20', '2017-3-24', 22, 2007, 'RESTAURANT');
  281. insert into booking values (3044, 1016, 8030, '2017-3-25', '2017-3-29', '2017-4-1', 21, 2012, 'SELF');
  282. insert into booking values (3045, 1011, 8001, '2017-4-4', '2017-4-8', '2017-4-12', 25, 2002, 'RESTAURANT');
  283. insert into booking values (3046, 1009, 8002, '2017-4-13', '2017-5-18', '2017-5-22', 17, 2025, 'RESTAURANT');
  284. insert into booking values (3047, 1015, 8002, '2017-6-25', '2017-7-1', '2017-7-3', 24, 2002, 'RESTAURANT');
  285. insert into booking values (3048, 1022, 8003, '2017-8-6', '2017-8-11', '2017-8-13', 29, 2012, 'SELF');
  286. insert into booking values (3049, 1014, 8004, '2017-8-15', '2017-8-20', '2017-8-23', 16, 2008, 'RESTAURANT');
  287. insert into booking values (3050, 1006, NULL, '2017-9-26', '2017-9-27', '2017-9-29', 22, 2018, 'RESTAURANT');
  288.    
  289. insert into rating values(3001, 4, NULL);
  290. insert into rating values(3002, 5, NULL);
  291. insert into rating values(3003, 4, "Hotel was extremely nice, will definitely visit again!");
  292. insert into rating values(3004, 5, NULL);
  293. insert into rating values(3005, 1, "Staff was very rude");
  294. insert into rating values(3006, 3, NULL);
  295. insert into rating values(3007, 4, "Conference Hall very spacious and well equipped, very glad we chose EMC.");
  296. insert into rating values(3008, 3, NULL);
  297. insert into rating values(3009, 4, NULL);
  298. insert into rating values(3010, 1, "Computers very slow and dated, please upgrade!");
  299. insert into rating values(3011, 4, NULL);
  300. insert into rating values(3012, 2, NULL);
  301. insert into rating values(3013, 3, NULL);
  302. insert into rating values(3014, 4, NULL);
  303. insert into rating values(3015, 4, NULL);
  304. insert into rating values(3016, 5, "Paintball was very fun, even got to shoot the boss!");
  305. insert into rating values(3017, 4, NULL);
  306. insert into rating values(3018, 3, "Paintball was fun, but pricing was a little bit expensive.");
  307. insert into rating values(3019, 4, NULL);
  308. insert into rating values(3020, 4, NULL);
  309. insert into rating values(3021, 3, NULL);
  310. insert into rating values(3022, 1, NULL);
  311. insert into rating values(3023, 2, "Computers in lab 2 very unresponsive and slow!");
  312. insert into rating values(3024, 5, NULL);
  313. insert into rating values(3025, 4, NULL);
  314. insert into rating values(3026, 2, NULL);
  315. insert into rating values(3027, 5, NULL);
  316. insert into rating values(3028, 4, NULL);
  317. insert into rating values(3029, 5, NULL);
  318. insert into rating values(3030, 3, NULL);
  319. insert into rating values(3031, 4, NULL);
  320. insert into rating values(3032, 2, "We stayed at Dotty's camping ground and it was very unsanitary and dirty.");
  321. insert into rating values(3033, 3, NULL);
  322. insert into rating values(3034, 3, NULL);
  323. insert into rating values(3035, 2, NULL);
  324. insert into rating values(3036, 1, "Camping ground very dirty, even saw multiple rats crawling about!");
  325. insert into rating values(3037, 2, NULL);
  326. insert into rating values(3038, 3, NULL);
  327. insert into rating values(3039, 5, NULL);
  328. insert into rating values(3040, 5, NULL);
  329. insert into rating values(3041, 4, NULL);
  330. insert into rating values(3042, 4, NULL);
  331. insert into rating values(3043, 4, NULL);
  332. insert into rating values(3044, 3, NULL);
  333. insert into rating values(3045, 3, NULL);
  334. insert into rating values(3046, 3, NULL);
  335. insert into rating values(3047, 5, NULL);
  336. insert into rating values(3048, 2, "I think we slept with rats at some points in the night");
  337. insert into rating values(3049, 2, NULL);
  338. insert into rating values(3050, 4, NULL);  
  339.  
  340. insert into facilities values (5001, 'Board Room A', 500);
  341. insert into facilities values (5002, 'Board Room B', 350);
  342. insert into facilities values (5003, 'Conference Hall', 800);
  343. insert into facilities values (5004, 'Computing Lab 1', 200);
  344. insert into facilities values (5005, 'Computing Lab 2', 150);
  345. insert into facilities values (5006, 'Computing Lab 3', 150);
  346. insert into facilities values (5007, 'Multimedia Equipment', 70);
  347.  
  348. insert into outdoors values (6001, 'Wall-climbing', 100);
  349. insert into outdoors values (6002, 'Paintball', 60);
  350. insert into outdoors values (6003, 'Canoeing', 60);
  351. insert into outdoors values (6004, 'Swimming', 60);
  352. insert into outdoors values (6005, 'Basketball', 40);
  353. insert into outdoors values (6006, 'Football', 40);
  354.  
  355. insert into staff values (7001, 'Madaline', 'FULL-TIME', 6001, 2000);
  356. insert into staff values (7002, 'Daine', 'FULL-TIME', 6002, 2000);
  357. insert into staff values (7003, 'Ardelia', 'PART-TIME', 6003, 1000);
  358. insert into staff values (7004, 'Kellye', 'FULL-TIME', 6004, 2500);
  359. insert into staff values (7005, 'Noelia', 'FULL-TIME', 6005, 2000);
  360. insert into staff values (7006, 'Alexandria', 'PART-TIME', 6006, 1000);
  361. insert into staff values (7007, 'Anabel', 'PART-TIME', 6003, 800);
  362. insert into staff values (7008, 'Breanne', 'FULL-TIME', 6004, 1900);
  363. insert into staff values (7009, 'Roselia', 'PART-TIME', 6002, 900);
  364. insert into staff values (7010, 'Ronald', 'PART-TIME', 6002, 1000);
  365.  
  366. insert into booking_facilities values (3001, 5003, '2013-3-3');
  367. insert into booking_facilities values (3002, 5004, '2013-3-13');
  368. insert into booking_facilities values (3003, 5003, '2013-3-21');
  369. insert into booking_facilities values (3004, 5002, '2013-6-3');
  370. insert into booking_facilities values (3005, 5007, '2013-6-10');
  371. insert into booking_facilities values (3006, 5001, '2013-7-17');
  372. insert into booking_facilities values (3007, 5003, '2013-8-27');
  373. insert into booking_facilities values (3008, 5004, '2013-10-9');
  374. insert into booking_facilities values (3009, 5006, '2013-10-16');
  375. insert into booking_facilities values (3010, 5005, '2013-11-25');
  376. insert into booking_facilities values (3011, 5001, '2014-3-2');
  377. insert into booking_facilities values (3012, 5003, '2014-5-9');
  378. insert into booking_facilities values (3013, 5002, '2014-5-22');
  379. insert into booking_facilities values (3014, 5006, '2014-7-3');
  380. insert into booking_facilities values (3015, 5001, '2014-7-8');
  381. insert into booking_facilities values (3016, 5002, '2014-8-14');
  382. insert into booking_facilities values (3017, 5003, '2014-10-21');
  383. insert into booking_facilities values (3018, 5004, '2014-10-29');
  384. insert into booking_facilities values (3019, 5004, '2015-1-6');
  385. insert into booking_facilities values (3020, 5002, '2015-2-13');
  386. insert into booking_facilities values (3021, 5007, '2015-3-22');
  387. insert into booking_facilities values (3022, 5001, '2015-3-27');
  388. insert into booking_facilities values (3023, 5005, '2015-4-5');
  389. insert into booking_facilities values (3024, 5004, '2015-6-9');
  390. insert into booking_facilities values (3025, 5003, '2015-7-17');
  391. insert into booking_facilities values (3026, 5003, '2015-7-26');
  392. insert into booking_facilities values (3027, 5007, '2015-9-4');
  393. insert into booking_facilities values (3028, 5007, '2015-11-11');
  394. insert into booking_facilities values (3029, 5002, '2015-11-14');
  395. insert into booking_facilities values (3030, 5007, '2016-2-25');
  396.  
  397. insert into booking_outdoors values (3001, 6006, '2013-3-3');
  398. insert into booking_outdoors values (3002, 6006, '2013-3-12');
  399. insert into booking_outdoors values (3003, 6004, '2013-3-21');
  400. insert into booking_outdoors values (3003, 6003, '2013-3-22');
  401. insert into booking_outdoors values (3005, 6005, '2013-6-9');
  402. insert into booking_outdoors values (3006, 6005, '2013-7-17');
  403. insert into booking_outdoors values (3007, 6005, '2013-8-28');
  404. insert into booking_outdoors values (3007, 6005, '2013-8-28');
  405. insert into booking_outdoors values (3008, 6003, '2013-10-8');
  406. insert into booking_outdoors values (3010, 6005, '2013-11-25');
  407. insert into booking_outdoors values (3012, 6006, '2014-5-10');
  408. insert into booking_outdoors values (3014, 6005, '2014-7-2');
  409. insert into booking_outdoors values (3016, 6005, '2014-8-14');
  410. insert into booking_outdoors values (3016, 6001, '2014-8-15');
  411. insert into booking_outdoors values (3018, 6001, '2014-10-17');
  412. insert into booking_outdoors values (3019, 6004, '2015-1-6');
  413. insert into booking_outdoors values (3020, 6004, '2015-2-14');
  414. insert into booking_outdoors values (3021, 6004, '2015-3-23');
  415. insert into booking_outdoors values (3023, 6003, '2015-4-4');
  416. insert into booking_outdoors values (3023, 6004, '2015-4-5');
  417. insert into booking_outdoors values (3025, 6003, '2015-7-17');
  418. insert into booking_outdoors values (3025, 6004, '2015-7-17');
  419. insert into booking_outdoors values (3027, 6006, '2015-9-5');
  420. insert into booking_outdoors values (3029, 6002, '2015-11-15');
  421. insert into booking_outdoors values (3029, 6002, '2015-11-16');
  422. insert into booking_outdoors values (3030, 6001, '2016-2-24');
  423. insert into booking_outdoors values (3031, 6006, '2016-3-2');
  424. insert into booking_outdoors values (3032, 6002, '2016-3-10');
  425. insert into booking_outdoors values (3034, 6004, '2016-6-10');
  426. insert into booking_outdoors values (3036, 6004, '2016-9-5');
  427.  
  428. insert into booking_snoopyLupiz values (3003, 3, 10, 0, 0);
  429. insert into booking_snoopyLupiz values (3011, 25, 0, 1, 1);
  430. insert into booking_snoopyLupiz values (3016, 0, 9, 0, 0);
  431.  
  432. -- ///////////////////////////////////////////////////////////////////////////
  433.  
  434. -- business process change oct 2018 task (3):
  435.  
  436. call getRevenue();
  437.  
  438. -- ///////////////////////////////////////////////////////////////////////////
  439.  
  440. -- generating sql reports
  441.  
  442. -- make sure to invoke procedure definition queries in procedure2.sql before calling these procedures
  443.  
  444. call getMostServiceTransaction(3);          -- 1 is FAMILY  | 2 is BUSINESS     | 3 is SCHOOL           | 4 is YOUTH GROUP
  445. call getClientList();
  446. call getAccTotal(2);                        -- 1 is HOTEL   | 2 is HOSTEL/DORM  | 3 is CAMPING GROUND
  447. call setIncreaseFacilities(0.05);
  448.  
  449. call getStaffBookingMade();
  450. call getMaxSalesEmployee;
  451.  
  452. call getLowRatings();
  453. call getMostRevenue();
  454. call getClientContact();
  455. call getStaffWages();
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!