Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- drop database if exists emc;
- create database emc;
- use emc;
- create table client_type (
- id int(4) PRIMARY KEY,
- clientTypeName varchar(255) NOT NULL);
- create table client (
- id int(4) PRIMARY KEY,
- type int(4) NOT NULL,
- name varchar(255) NOT NULL,
- address varchar(255) NOT NULL,
- repName varchar(255) NOT NULL,
- repContact int NOT NULL UNIQUE,
- CONSTRAINT clientType_fk FOREIGN KEY (type) REFERENCES client_type(id));
- create table client_contact (
- name varchar(255) NOT NULL,
- contact int NOT NULL UNIQUE,
- clientID int(4) NOT NULL,
- email varchar(255) NULL,
- CONSTRAINT clientID_fk FOREIGN KEY (clientID) REFERENCES client(id));
- create table acc_type (
- id int(4) PRIMARY KEY,
- name varchar(255) NOT NULL);
- create table acc (
- id int(4) PRIMARY KEY,
- accType int(4) NOT NULL,
- name varchar(255) NOT NULL,
- maxOccupants int NOT NULL,
- charge smallint NOT NULL,
- CONSTRAINT accType_fk FOREIGN KEY (accType) REFERENCES acc_type(id));
- create table advisor (
- id int(4) PRIMARY KEY,
- name varchar(255) NOT NULL);
- create table booking (
- id int(4) PRIMARY KEY,
- client int(4) NOT NULL,
- advisor int(4) NULL,
- bookingDate date NOT NULL,
- startDate date NOT NULL,
- endDate date NOT NULL,
- totalPeople int NOT NULL,
- acc int(4) NOT NULL,
- catering varchar(255) NOT NULL,
- CONSTRAINT client_fk FOREIGN KEY (client) REFERENCES client(id),
- CONSTRAINT acc_fk FOREIGN KEY (acc) REFERENCES acc(id),
- CONSTRAINT advisor_fk FOREIGN KEY (advisor) REFERENCES advisor(id),
- CONSTRAINT my_catering CHECK (catering = 'RESTAURANT' OR catering = 'SELF'));
- create table rating (
- bookingId int(4) NOT NULL,
- rating int(1) NOT NULL,
- note varchar(255) NULL,
- CONSTRAINT bookingIdr_fk FOREIGN KEY (bookingId) REFERENCES booking(id));
- create table facilities (
- id int(4) PRIMARY KEY,
- name varchar(255) NOT NULL,
- charge smallint NOT NULL);
- create table outdoors (
- id int(4) PRIMARY KEY,
- name varchar(255) NOT NULL,
- charge smallint NOT NULL);
- create table staff (
- id int(4) PRIMARY KEY,
- name varchar(255) NOT NULL,
- contract varchar(255) NOT NULL,
- supervisor int(4) NOT NULL,
- CONSTRAINT my_contract CHECK (contract = 'PART-TIME' OR contract = 'FULL-TIME'),
- CONSTRAINT supervisor_fk FOREIGN KEY (supervisor) REFERENCES outdoors(id));
- create table booking_facilities (
- bookingID int(4) NOT NULL,
- facilities int(4) NOT NULL,
- date date NOT NULL,
- CONSTRAINT bookingfacID_fk FOREIGN KEY (bookingID) REFERENCES booking(id),
- CONSTRAINT facilities_fk FOREIGN KEY (facilities) REFERENCES facilities(id));
- create table booking_outdoors (
- bookingID int(4) NOT NULL,
- outdoors int(4) NOT NULL,
- date date NOT NULL,
- CONSTRAINT bookingoutID_fk FOREIGN KEY (bookingID) REFERENCES booking(id),
- CONSTRAINT outdoors_fk FOREIGN KEY (outdoors) REFERENCES outdoors(id));
- create table booking_snoopyLupiz (
- bookingID int(4) NOT NULL,
- singleroom tinyint NULL,
- doubleroom tinyint NULL,
- kingsuite tinyint NULL,
- luxurysuite tinyint NULL,
- CONSTRAINT bookingID_fk FOREIGN KEY (bookingID) REFERENCES booking(id));
- insert into client_type values (0001, 'Family');
- insert into client_type values (0002, 'Business');
- insert into client_type values (0003, 'School');
- insert into client_type values (0004, 'Youth Group');
- insert into client values (1001, 3, 'Maira\'s International School', 'Washington', 'Tyson', 179184289);
- insert into client values (1002, 1, 'Devon and Family', 'Springfield', 'Aurore', 189373252);
- insert into client values (1003, 3, 'Verlie\'s International School', 'Springfield', 'Suk', 145797438);
- insert into client values (1004, 4, 'Daniela Youth Group', 'Bristol', 'Nichelle', 122719731);
- insert into client values (1005, 3, 'Dakota\'s International School', 'Dover', 'Mathilde', 177414314);
- insert into client values (1006, 1, 'Karlene and Family', 'Oxford', 'Myung', 124888779);
- insert into client values (1007, 4, 'Lavina Youth Group', 'Oxford', 'Lynda', 115942338);
- insert into client values (1008, 2, 'Tiffiny and Co.', 'Lexington', 'Constance', 115169518);
- insert into client values (1009, 2, 'Alfonso and Co.', 'Springfield', 'Jovita', 166417485);
- insert into client values (1010, 1, 'Avery and Family', 'Washington', 'Jana', 138441668);
- insert into client values (1011, 1, 'Damon and Family', 'Hudson', 'Joycelyn', 158374862);
- insert into client values (1012, 4, 'Damaris Youth Group', 'Bristol', 'Lorene', 129897562);
- insert into client values (1013, 1, 'Gil and Family', 'Oakland', 'Jama', 161398593);
- insert into client values (1014, 4, 'Ashton Youth Group', 'Madison', 'Sharda', 145352146);
- insert into client values (1015, 3, 'Marcell\'s International School', 'Oxford', 'Ashlee', 199326331);
- insert into client values (1016, 2, 'Francesco and Co.', 'Auburn', 'Garth', 132431419);
- insert into client values (1017, 1, 'Tricia and Family', 'Manchester', 'Ka', 174244989);
- insert into client values (1018, 3, 'Larry\'s International School', 'Washington', 'Wendie', 111884618);
- insert into client values (1019, 4, 'Clifton Youth Group', 'Springfield', 'Werner', 149512627);
- insert into client values (1020, 2, 'Serena and Co.', 'Manchester', 'Carlos', 141724562);
- insert into client values (1021, 4, 'Howard Youth Group', 'Burlington', 'Toshia', 157678296);
- insert into client values (1022, 1, 'Lourie and Family', 'Jackson', 'Bianca', 162998631);
- insert into client values (1023, 3, 'Yelena\'s International School', 'Oxford', 'Barb', 117572587);
- insert into client values (1024, 3, 'Krysta\'s International School', 'Washington', 'Cristine', 162822896);
- insert into client values (1025, 4, 'Dawn Youth Group', 'Bristol', 'Ashlea', 146453655);
- insert into client values (1026, 1, 'Magen and Family', 'Springfield', 'Thad', 136565338);
- insert into client values (1027, 2, 'Ardelia and Co.', 'Oxford', 'Omer', 158513664);
- insert into client values (1028, 3, 'Hiram\'s International School', 'Georgetown', 'Foster', 143265599);
- insert into client values (1029, 3, 'Rhea\'s International School', 'Auburn', 'Sharen', 121272177);
- insert into client values (1030, 4, 'Millard Youth Group', 'Madison', 'Rolanda', 129812855);
- insert into client_contact values ('Cinthia', 171684565, 1003, NULL);
- insert into client_contact values ('Sadie', 152444456, 1029, 'Sadie@gmail.com');
- insert into client_contact values ('Roger', 187659462, 1006, 'Roger@gmail.com');
- insert into client_contact values ('Kattie', 182222762, 1030, NULL);
- insert into client_contact values ('Lila', 185587429, 1027, NULL);
- insert into client_contact values ('Brice', 191253153, 1026, NULL);
- insert into client_contact values ('Rosamond', 146625519, 1020, 'Rosamond@gmail.com');
- insert into client_contact values ('Frank', 122615294, 1027, NULL);
- insert into client_contact values ('Tracy', 112837337, 1029, NULL);
- insert into client_contact values ('Shenika', 197844692, 1013, NULL);
- insert into client_contact values ('Criselda', 116755121, 1013, 'Criselda@gmail.com');
- insert into client_contact values ('Myron', 184451318, 1028, NULL);
- insert into client_contact values ('Tesha', 184689376, 1025, NULL);
- insert into client_contact values ('Virginia', 191517761, 1003, 'Virginia@gmail.com');
- insert into client_contact values ('Donald', 173973382, 1017, NULL);
- insert into client_contact values ('Salvador', 157119578, 1001, 'Salvador@gmail.com');
- insert into client_contact values ('Twanna', 182114668, 1010, NULL);
- insert into client_contact values ('Alycia', 145814292, 1006, NULL);
- insert into client_contact values ('Glory', 126996977, 1028, 'Glory@gmail.com');
- insert into client_contact values ('Hermila', 127735291, 1029, 'Hermila@gmail.com');
- insert into client_contact values ('Jan', 164172963, 1018, NULL);
- insert into client_contact values ('Neil', 182768369, 1020, NULL);
- insert into client_contact values ('Oralee', 132325859, 1024, 'Oralee@gmail.com');
- insert into client_contact values ('Rina', 186357158, 1005, NULL);
- insert into client_contact values ('Ingeborg', 177848639, 1026, 'Ingeborg@gmail.com');
- insert into client_contact values ('Kathrin', 145384391, 1006, 'Kathrin@gmail.com');
- insert into client_contact values ('Kerry', 112627879, 1027, 'Kerry@gmail.com');
- insert into client_contact values ('Marylou', 181319913, 1020, NULL);
- insert into client_contact values ('Jackie', 184521224, 1030, 'Jackie@gmail.com');
- insert into client_contact values ('Florencio', 127816595, 1006, 'Florencio@gmail.com');
- insert into acc_type values (0001, 'Hotel');
- insert into acc_type values (0002, 'Hostel');
- insert into acc_type values (0003, 'Camping Ground');
- insert into acc values (2001, 1, 'Snoopy Lupiz' , 230, 100);
- insert into acc values (2002, 2, 'Laquanda\'s Hostel' , 48, 70);
- insert into acc values (2003, 2, 'Nathanial\'s Hostel' , 40, 70);
- insert into acc values (2004, 3, 'Joy\'s Camping Ground' , 42, 40);
- insert into acc values (2005, 3, 'Lanie\'s Camping Ground' , 39, 25);
- insert into acc values (2006, 2, 'Fletcher\'s Hostel' , 46, 60);
- insert into acc values (2007, 2, 'Lesley\'s Hostel' , 42, 50);
- insert into acc values (2008, 3, 'Berna\'s Camping Ground' , 30, 40);
- insert into acc values (2009, 2, 'Terisa\'s Hostel' , 39, 45);
- insert into acc values (2010, 3, 'Brigida\'s Camping Ground' , 41, 30);
- insert into acc values (2011, 2, 'Viviana\'s Hostel' , 39, 65);
- insert into acc values (2012, 2, 'Kecia\'s Hostel' , 35, 85);
- insert into acc values (2013, 3, 'Ivan\'s Camping Ground' , 44, 35);
- insert into acc values (2014, 3, 'Verdell\'s Camping Ground' , 34, 20);
- insert into acc values (2015, 3, 'Nicola\'s Camping Ground' , 34, 25);
- insert into acc values (2016, 3, 'Marie\'s Camping Ground' , 43, 20);
- insert into acc values (2017, 3, 'Sherrill\'s Camping Ground' , 49, 25);
- insert into acc values (2018, 2, 'Hyo\'s Hostel' , 39, 70);
- insert into acc values (2019, 3, 'Madlyn\'s Camping Ground' , 31, 25);
- insert into acc values (2020, 2, 'Veronica\'s Hostel' , 36, 85);
- insert into acc values (2021, 3, 'Luvenia\'s Camping Ground' , 42, 25);
- insert into acc values (2022, 2, 'Elijah\'s Hostel' , 43, 55);
- insert into acc values (2023, 3, 'Jolanda\'s Camping Ground' , 48, 30);
- insert into acc values (2024, 3, 'Renaldo\'s Camping Ground' , 40, 30);
- insert into acc values (2025, 2, 'Vella\'s Hostel' , 31, 65);
- insert into acc values (2026, 2, 'Edith\'s Hostel' , 47, 60);
- insert into acc values (2027, 3, 'Millicent\'s Camping Ground' , 39, 50);
- insert into acc values (2028, 3, 'Dotty\'s Camping Ground' , 40, 35);
- insert into acc values (2029, 2, 'Samella\'s Hostel' , 45, 60);
- insert into acc values (2030, 3, 'Darryl\'s Camping Ground' , 31, 15);
- insert into advisor values (8001, 'Gordon');
- insert into advisor values (8002, 'Dottie');
- insert into advisor values (8003, 'Thomasine');
- insert into advisor values (8004, 'Brittany');
- insert into advisor values (8005, 'Nell');
- insert into advisor values (8006, 'Nannette');
- insert into advisor values (8007, 'Shery');
- insert into advisor values (8008, 'Randell');
- insert into advisor values (8009, 'Halley');
- insert into advisor values (8010, 'Martina');
- insert into advisor values (8011, 'Alvina');
- insert into advisor values (8012, 'Ester');
- insert into advisor values (8013, 'Letisha');
- insert into advisor values (8014, 'Brande');
- insert into advisor values (8015, 'Louanne');
- insert into advisor values (8016, 'Lawerence');
- insert into advisor values (8017, 'Camellia');
- insert into advisor values (8018, 'Bart');
- insert into advisor values (8019, 'Edith');
- insert into advisor values (8020, 'Adella');
- insert into advisor values (8021, 'Caroyln');
- insert into advisor values (8022, 'Mike');
- insert into advisor values (8023, 'Senaida');
- insert into advisor values (8024, 'Tania');
- insert into advisor values (8025, 'Odelia');
- insert into advisor values (8026, 'Melonie');
- insert into advisor values (8027, 'Herlinda');
- insert into advisor values (8028, 'Ellan');
- insert into advisor values (8029, 'Forrest');
- insert into advisor values (8030, 'Tyron');
- select bookingID from booking_outdoors where outdoors = 6001;
- insert into booking values (3001, 1001, 8001, '2013-2-1', '2013-3-2', '2013-3-4', 18, 2014, 'SELF');
- insert into booking values (3002, 1014, 8001, '2013-3-7', '2013-3-12', '2013-3-14', 25, 2010, 'RESTAURANT');
- insert into booking values (3003, 1007, 8001, '2013-3-17', '2013-3-20', '2013-3-24', 23, 2001, 'RESTAURANT');
- insert into booking values (3004, 1030, NULL, '2013-4-27', '2013-6-1', '2013-6-4', 15, 2008, 'RESTAURANT');
- insert into booking values (3005, 1016, 8003, '2013-6-6', '2013-6-9', '2013-6-13', 27, 2015, 'RESTAURANT');
- insert into booking values (3006, 1011, 8004, '2013-7-14', '2013-7-16', '2013-7-20', 21, 2015, 'RESTAURANT');
- insert into booking values (3007, 1030, 8005, '2013-8-22', '2013-8-27', '2013-9-1', 21, 2006, 'SELF');
- insert into booking values (3008, 1008, 8006, '2013-9-4', '2013-10-8', '2013-10-12', 20, 2003, 'SELF');
- insert into booking values (3009, 1013, NULL, '2013-10-13', '2013-10-16', '2013-10-20', 22, 2017, 'RESTAURANT');
- insert into booking values (3010, 1018, 8007, '2013-10-20', '2013-11-24', '2013-11-26', 15, 2030, 'RESTAURANT');
- insert into booking values (3011, 1003, 8007, '2014-1-28', '2014-3-1', '2014-3-4', 30, 2001, 'SELF');
- insert into booking values (3012, 1004, 8008, '2014-4-4', '2014-5-9', '2014-5-13', 21, 2010, 'SELF');
- insert into booking values (3013, 1028, 8009, '2014-5-16', '2014-5-21', '2014-5-24', 16, 2004, 'RESTAURANT');
- insert into booking values (3014, 1004, 8010, '2014-6-27', '2014-7-1', '2014-7-4', 21, 2016, 'SELF');
- insert into booking values (3015, 1007, 8010, '2014-7-5', '2014-7-8', '2014-7-12', 17, 2018, 'RESTAURANT');
- insert into booking values (3016, 1027, 8011, '2014-8-12', '2014-8-13', '2014-8-17', 18, 2001, 'RESTAURANT');
- insert into booking values (3017, 1002, NULL, '2014-9-17', '2014-10-21', '2014-10-23', 16, 2006, 'SELF');
- insert into booking values (3018, 1012, NULL, '2014-10-26', '2014-10-27', '2014-11-1', 19, 2006, 'RESTAURANT');
- insert into booking values (3019, 1025, 8012, '2015-1-2', '2015-1-5', '2015-1-8', 25, 2003, 'RESTAURANT');
- insert into booking values (3020, 1006, 8013, '2015-2-9', '2015-2-13', '2015-2-17', 16, 2020, 'SELF');
- insert into booking values (3021, 1010, 8013, '2015-3-17', '2015-3-22', '2015-3-24', 27, 2006, 'SELF');
- insert into booking values (3022, 1002, 8015, '2015-3-25', '2015-3-26', '2015-3-29', 15, 2024, 'RESTAURANT');
- insert into booking values (3023, 1018, 8016,'2015-4-1', '2015-4-4', '2015-4-6', 16, 2014, 'SELF');
- insert into booking values (3024, 1024, 8017, '2015-5-9', '2015-6-9', '2015-6-13', 16, 2016, 'SELF');
- insert into booking values (3025, 1017, 8018,'2015-6-13', '2015-7-16', '2015-7-19', 29, 2023, 'SELF');
- insert into booking values (3026, 1023, 8018, '2015-7-21', '2015-7-25', '2015-7-29', 26, 2025, 'RESTAURANT');
- insert into booking values (3027, 1030, 8019, '2015-9-1', '2015-9-3', '2015-9-5', 23, 2003, 'RESTAURANT');
- insert into booking values (3028, 1009, 8020, '2015-10-6', '2015-11-10', '2015-11-12', 19, 2005, 'SELF');
- insert into booking values (3029, 1001, NULL, '2015-11-14', '2015-11-14', '2015-11-18', 19, 2015, 'RESTAURANT');
- insert into booking values (3030, 1030, 8021,'2016-1-20', '2016-2-24', '2016-2-27', 29, 2005, 'RESTAURANT');
- insert into booking values (3031, 1005, 8021, '2016-2-29', '2016-3-1', '2016-3-3', 26, 2026, 'SELF');
- insert into booking values (3032, 1019, 8022, '2016-3-4', '2016-3-9', '2016-3-13', 17, 2019, 'SELF');
- insert into booking values (3033, 1006, 8023, '2016-4-13', '2016-5-13', '2016-5-16', 20, 2028, 'RESTAURANT');
- insert into booking values (3034, 1029, 8024, '2016-5-18', '2016-6-19', '2016-6-22', 30, 2018, 'RESTAURANT');
- insert into booking values (3035, 1005, NULL, '2016-6-25', '2016-7-25', '2016-7-29', 15, 2027, 'RESTAURANT');
- insert into booking values (3036, 1020, 8025, '2016-8-1', '2016-9-4', '2016-9-8', 20, 2028, 'SELF');
- insert into booking values (3037, 1021, 8026, '2016-9-9', '2016-10-12', '2016-10-14', 26, 2023, 'SELF');
- insert into booking values (3038, 1023, 8026, '2016-10-17', '2016-11-18', '2016-11-20', 30, 2024, 'SELF');
- insert into booking values (3039, 1003, 8027, '2016-11-20', '2016-11-25', '2016-11-27', 19, 2025, 'SELF');
- insert into booking values (3040, 1026, 8028, '2016-11-28', '2017-1-1', '2017-1-5', 29, 2013, 'SELF');
- insert into booking values (3041, 1009, NULL, '2017-2-6', '2017-2-6', '2017-2-8', 19, 2014, 'RESTAURANT');
- insert into booking values (3042, 1004, 8029, '2017-2-10', '2017-2-12', '2017-2-15', 27, 2005, 'SELF');
- insert into booking values (3043, 1004, 8030, '2017-3-18', '2017-3-20', '2017-3-24', 22, 2007, 'RESTAURANT');
- insert into booking values (3044, 1016, 8030, '2017-3-25', '2017-3-29', '2017-4-1', 21, 2012, 'SELF');
- insert into booking values (3045, 1011, 8001, '2017-4-4', '2017-4-8', '2017-4-12', 25, 2002, 'RESTAURANT');
- insert into booking values (3046, 1009, 8002, '2017-4-13', '2017-5-18', '2017-5-22', 17, 2025, 'RESTAURANT');
- insert into booking values (3047, 1015, 8002, '2017-6-25', '2017-7-1', '2017-7-3', 24, 2002, 'RESTAURANT');
- insert into booking values (3048, 1022, 8003, '2017-8-6', '2017-8-11', '2017-8-13', 29, 2012, 'SELF');
- insert into booking values (3049, 1014, 8004, '2017-8-15', '2017-8-20', '2017-8-23', 16, 2008, 'RESTAURANT');
- insert into booking values (3050, 1006, NULL, '2017-9-26', '2017-9-27', '2017-9-29', 22, 2018, 'RESTAURANT');
- insert into rating values(3001, 4, "");
- insert into rating values(3002, 5, "");
- insert into rating values(3003, 4, "Hotel was extremely nice, will definitely visit again!");
- insert into rating values(3004, 5, "");
- insert into rating values(3005, 1, "Staff was very rude");
- insert into rating values(3006, 3, "");
- insert into rating values(3007, 4, "Conference Hall very spacious and well equipped, very glad we chose EMC.");
- insert into rating values(3008, 3, "");
- insert into rating values(3009, 4, NULL);
- insert into rating values(3010, 1, "Computers very slow and dated, please upgrade!");
- insert into rating values(3011, 4, NULL);
- insert into rating values(3012, 2, NULL);
- insert into rating values(3013, 3, "");
- insert into rating values(3014, 4, "");
- insert into rating values(3015, 4, NULL);
- insert into rating values(3016, 5, "Paintball was very fun, even got to shoot the boss!");
- insert into rating values(3017, 4, NULL);
- insert into rating values(3018, 3, "Paintball was fun, but pricing was a little bit expensive.");
- insert into rating values(3019, 4, "");
- insert into rating values(3020, 4, NULL);
- insert into rating values(3021, 3, "");
- insert into rating values(3022, 1, NULL);
- insert into rating values(3023, 2, "Computers in lab 2 very unresponsive and slow!");
- insert into rating values(3024, 5, "");
- insert into rating values(3025, 4, NULL);
- insert into rating values(3026, 2, NULL);
- insert into rating values(3027, 5, NULL);
- insert into rating values(3028, 4, NULL);
- insert into rating values(3029, 5, "");
- insert into rating values(3030, 3, NULL);
- insert into rating values(3031, 4, NULL);
- insert into rating values(3032, 2, "We stayed at Dotty's camping ground and it was very unsanitary and dirty.");
- insert into rating values(3033, 3, NULL);
- insert into rating values(3034, 3, NULL);
- insert into rating values(3035, 2, NULL);
- insert into rating values(3036, 1, "Camping ground very dirty, even saw multiple rats crawling about!");
- insert into rating values(3037, 2, NULL);
- insert into rating values(3038, 3, "");
- insert into rating values(3039, 5, "");
- insert into rating values(3040, 5, "");
- insert into rating values(3041, 4, NULL);
- insert into rating values(3042, 4, "");
- insert into rating values(3043, 4, NULL);
- insert into rating values(3044, 3, "");
- insert into rating values(3045, 3, NULL);
- insert into rating values(3046, 3, NULL);
- insert into rating values(3047, 5, NULL);
- insert into rating values(3048, 2, "");
- insert into rating values(3049, 2, NULL);
- insert into rating values(3050, 4, "");
- insert into facilities values (5001, 'Board Room A', 500);
- insert into facilities values (5002, 'Board Room B', 350);
- insert into facilities values (5003, 'Conference Hall', 800);
- insert into facilities values (5004, 'Computing Lab 1', 200);
- insert into facilities values (5005, 'Computing Lab 2', 150);
- insert into facilities values (5006, 'Computing Lab 3', 150);
- insert into facilities values (5007, 'Multimedia Equipment', 70);
- insert into outdoors values (6001, 'Wall-climbing', 100);
- insert into outdoors values (6002, 'Paintball', 60);
- insert into outdoors values (6003, 'Canoeing', 60);
- insert into outdoors values (6004, 'Swimming', 60);
- insert into outdoors values (6005, 'Basketball', 40);
- insert into outdoors values (6006, 'Football', 40);
- insert into staff values (7001, 'Madaline', 'FULL-TIME', 6001);
- insert into staff values (7002, 'Daine', 'FULL-TIME', 6002);
- insert into staff values (7003, 'Ardelia', 'PART-TIME', 6003);
- insert into staff values (7004, 'Kellye', 'FULL-TIME', 6004);
- insert into staff values (7005, 'Noelia', 'FULL-TIME', 6005);
- insert into staff values (7006, 'Alexandria', 'PART-TIME', 6006);
- insert into staff values (7007, 'Anabel', 'PART-TIME', 6003);
- insert into staff values (7008, 'Breanne', 'FULL-TIME', 6004);
- insert into staff values (7009, 'Roselia', 'PART-TIME', 6002);
- insert into staff values (7010, 'Ronald', 'PART-TIME', 6002);
- insert into booking_facilities values (3001, 5003, '2013-3-3');
- insert into booking_facilities values (3002, 5004, '2013-3-13');
- insert into booking_facilities values (3003, 5003, '2013-3-21');
- insert into booking_facilities values (3004, 5002, '2013-6-3');
- insert into booking_facilities values (3005, 5007, '2013-6-10');
- insert into booking_facilities values (3006, 5001, '2013-7-17');
- insert into booking_facilities values (3007, 5003, '2013-8-27');
- insert into booking_facilities values (3008, 5004, '2013-10-9');
- insert into booking_facilities values (3009, 5006, '2013-10-16');
- insert into booking_facilities values (3010, 5005, '2013-11-25');
- insert into booking_facilities values (3011, 5001, '2014-3-2');
- insert into booking_facilities values (3012, 5003, '2014-5-9');
- insert into booking_facilities values (3013, 5002, '2014-5-22');
- insert into booking_facilities values (3014, 5006, '2014-7-3');
- insert into booking_facilities values (3015, 5001, '2014-7-8');
- insert into booking_facilities values (3016, 5002, '2014-8-14');
- insert into booking_facilities values (3017, 5003, '2014-10-21');
- insert into booking_facilities values (3018, 5004, '2014-10-29');
- insert into booking_facilities values (3019, 5004, '2015-1-6');
- insert into booking_facilities values (3020, 5002, '2015-2-13');
- insert into booking_facilities values (3021, 5007, '2015-3-22');
- insert into booking_facilities values (3022, 5001, '2015-3-27');
- insert into booking_facilities values (3023, 5005, '2015-4-5');
- insert into booking_facilities values (3024, 5004, '2015-6-9');
- insert into booking_facilities values (3025, 5003, '2015-7-17');
- insert into booking_facilities values (3026, 5003, '2015-7-26');
- insert into booking_facilities values (3027, 5007, '2015-9-4');
- insert into booking_facilities values (3028, 5007, '2015-11-11');
- insert into booking_facilities values (3029, 5002, '2015-11-14');
- insert into booking_facilities values (3030, 5007, '2016-2-25');
- insert into booking_outdoors values (3001, 6006, '2013-3-3');
- insert into booking_outdoors values (3002, 6006, '2013-3-12');
- insert into booking_outdoors values (3003, 6004, '2013-3-21');
- insert into booking_outdoors values (3003, 6003, '2013-3-22');
- insert into booking_outdoors values (3005, 6005, '2013-6-9');
- insert into booking_outdoors values (3006, 6005, '2013-7-17');
- insert into booking_outdoors values (3007, 6005, '2013-8-28');
- insert into booking_outdoors values (3007, 6005, '2013-8-28');
- insert into booking_outdoors values (3008, 6003, '2013-10-8');
- insert into booking_outdoors values (3010, 6005, '2013-11-25');
- insert into booking_outdoors values (3012, 6006, '2014-5-10');
- insert into booking_outdoors values (3014, 6005, '2014-7-2');
- insert into booking_outdoors values (3016, 6005, '2014-8-14');
- insert into booking_outdoors values (3016, 6001, '2014-8-15');
- insert into booking_outdoors values (3018, 6001, '2014-10-17');
- insert into booking_outdoors values (3019, 6004, '2015-1-6');
- insert into booking_outdoors values (3020, 6004, '2015-2-14');
- insert into booking_outdoors values (3021, 6004, '2015-3-23');
- insert into booking_outdoors values (3023, 6003, '2015-4-4');
- insert into booking_outdoors values (3023, 6004, '2015-4-5');
- insert into booking_outdoors values (3025, 6003, '2015-7-17');
- insert into booking_outdoors values (3025, 6004, '2015-7-17');
- insert into booking_outdoors values (3027, 6006, '2015-9-5');
- insert into booking_outdoors values (3029, 6002, '2015-11-15');
- insert into booking_outdoors values (3029, 6002, '2015-11-16');
- insert into booking_outdoors values (3030, 6001, '2016-2-24');
- insert into booking_outdoors values (3031, 6006, '2016-3-2');
- insert into booking_outdoors values (3032, 6002, '2016-3-10');
- insert into booking_outdoors values (3034, 6004, '2016-6-10');
- insert into booking_outdoors values (3036, 6004, '2016-9-5');
- insert into booking_snoopyLupiz values (3003, 3, 10, 0, 0);
- insert into booking_snoopyLupiz values (3011, 25, 0, 1, 1);
- insert into booking_snoopyLupiz values (3016, 0, 9, 0, 0);
- -- ///////////////////////////////////////////////////////////////////////////
- -- make sure to invoke procedure definition queries in procedure2.sql before calling these procedures
- call mostServiceTransaction(3); -- 1 is FAMILY | 2 is BUSINESS | 3 is SCHOOL | 4 is YOUTH GROUP
- call getClientList();
- call getAccTotal(2); -- 1 is HOTEL | 2 is HOSTEL/DORM | 3 is CAMPING GROUND
- call increaseFacilities(0.05);
- call staffBookingMade();
- call maxSales;
- -- check for rating in (1, 2) and retrieve contact details to ask about experience and possibly provide compensation
- -- or print out all ratings (and contact details and comments) and sort by rating
- -- look at which facilities/accommodation/outdoors activities generated the most revenue and possibly add more instances of these
- -- retrieve contact details of clients and send vouchers/promotional news/marketing
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement