SQL Script for Unit 11 Lab (Hotel Scholastica Reservation Database Records) This script will drop and...

Transcribed Image Text:

SQL Script for Unit 11 Lab (Hotel Scholastica Reservation Database Records) This script will drop and then re-create the tables for a Hotel database and add data to each table. Created by: David Vosen Created on: 11/14/2017 Modified by: David Vosen Modified on: 11/02/2018 Modified on: 11/14/2019 Modified on: 07/25/2020 Modified by: Student Name Modified on: Current Date -- DROP all the past Bookstore Database Tables IF OBJECT_ID ("payment') IF OBJECT_ID ('list') IF OBJECT ID ('line')) IF OBJECT ID ('invoice') IF OBJECT ID ('product') IF OBJECT_ID ('vendor') IF OBJECT_ID ("customer') IF OBJECT_ID IF OBJECT ID IF OBJECT_ID ("room") IF OBJECT_ID ("building')) IF OBJECT_ID ('property') -- DROP all tables for previous Hotel Reservation Database IF OBJECT_ID ("lov") IF OBJECT ID ('room_reservation') ("reservation') ('phone') IF OBJECT_ID ('guest_address') IF OBJECT ID ("guest") -- Create the Property table CREATE TABLE property ( prop_id prop_name prop_mgr prop_add1 prop_add2 prop_city prop_state prop_zip prop_phone list_status PRIMARY KEY (prop_id)); -- Create the building CREATE TABLE building ( building_id prop_id build_name build floors build air list_status table room_num room_type room_smoking room_rate list_status VARCHAR (10) VARCHAR (30), VARCHAR (40), VARCHAR (25), VARCHAR (25), VARCHAR (25), CHAR (2), VARCHAR(9), CHAR(10), CHAR (1) PRIMARY KEY (building_id), FOREIGN KEY (prop_id) -- Create the room table CREATE TABLE room ( room id building_id PRIMARY KEY (room_id), FOREIGN KEY (building_id) IS NOT NULL IS NOT NULL IS NOT NULL IS NOT NULL IS NOT NULL IS NOT NULL IS NOT NULL VARCHAR (10) VARCHAR (10) VARCHAR (25), INT, CHAR (1), IS NOT NULL IS NOT NULL DROP TABLE list; DROP TABLE line; DROP TABLE invoice; DROP TABLE product; DROP TABLE vendor; DROP TABLE customer; NOT NULL CHAR (1), CHAR (1) REFERENCES property); IS NOT NULL IS NOT NULL NOT NULL VARCHAR (10) VARCHAR (10) INT, VARCHAR (15), IS NOT NULL NOT NULL NOT NULL, IS NOT NULL IS NOT NULL IS NOT NULL IS NOT NULL DROP TABLE payment; NOT NULL MONEY, CHAR (1) REFERENCES building); NOT NULL UNIQUE, NOT NULL NOT NULL, DEFAULT 'A', UNIQUE, "/ DROP TABLE lov; DROP TABLE room_reservation; DROP TABLE reservation; DROP TABLE phone; DROP TABLE guest_address; DROP TABLE guest; DROP TABLE room; DROP TABLE building; DROP TABLE property; DEFAULT 'A', UNIQUE, DEFAULT 'A', -- Create the Guest table CREATE TABLE guest ( guest_id guest_fname guest iname guest_mi guest_dob guest_email list status PRIMARY KEY (guest_id)); -- Create the Address table CREATE TABLE guest_address ( address_id guest_id PRIMARY KEY (address_id), FOREIGN KEY (guest_id) PRIMARY KEY (phone_id), FOREIGN KEY (guest_id) VARCHAR (10) VARCHAR (20), VARCHAR (20), address_type VARCHAR (15), guest_addl VARCHAR (25), guest_add2 VARCHAR (25), guest_city guest_state guest_zip list_status CHAR (1), DATETIME, VARCHAR (100), CHAR (1) -- Create the Phone table CREATE TABLE phone ( phone_id guest_id phone_type phone_num list_status reservation_id guest_id res_type res_date -- -- Create table reservation CREATE TABLE reservation ( room_id res_rate list_status VARCHAR (10) VARCHAR (10) VARCHAR (25), CHAR(2), CHAR (1) VARCHAR (9), REFERENCES guest); VARCHAR (10) VARCHAR (10) VARCHAR (15), CHAR(10), CHAR (1) REFERENCES guest); VARCHAR (10) NOT NULL NOT NULL MONEY, checkin_date DATETIME, checkout_date DATETIME, cancelled_date DATETIME, cancel_fee created_date DATETIME, created by modified_date DATETIME, modified by PRIMARY KEY (reservation_id), FOREIGN KEY (guest_id) REFERENCES guest); VARCHAR (10) VARCHAR (15), DATETIME, reservation_id VARCHAR (10) -- Create table room_reservation bridge table CREATE TABLE room_reservation ( PRIMARY KEY (reservation_id, room_id), FOREIGN KEY (reservation_id) FOREIGN KEY (room_id) NOT NULL NOT NULL, NOT NULL VARCHAR(10), VARCHAR (10), -- Create the list of values table CREATE TABLE lov ( VARCHAR (10) MONEY, CHAR (1) VARCHAR (10) VARCHAR (20), NOT NULL NOT NULL, NOT NULL, NOT NULL VARCHAR (20), CHAR (1) UNIQUE, REFERENCES reservation, REFERENCES room); DEFAULT 'A' NOT NULL NOT NULL, UNIQUE, NOT NULL NOT NULL, UNIQUE, NOT NULL list_id list_name list_display VARCHAR (20), list_value list_status PRIMARY KEY (list_id)); With the tables created, the script now populates each table NOT NULL UNIQUE, DEFAULT 'A', DEFAULT 'A', DEFAULT 'A', UNIQUE, DEFAULT 'A', -- Create hotel property records INSERT INTO property (prop_id, prop_name, prop_mgr, prop_addl, prop_add2, prop_city, prop_state, prop_zip, prop_phone, list_status) VALUES (*1000000001', 'Hotel Scholastica - Duluth', 'Dr. Barbara McDonald', '1200 Kenwood Avenue', NULL, 'Duluth', 'MN', '55811', '8004475444', 'A'); INSERT INTO property (prop_id, prop_name, prop_mgr, prop_addl, prop_add2, prop_city, prop_state, prop_zip, prop_phone, list_status) VALUES ('1000000002", 'Hotel Scholastica - St. Paul', 'Thomas Brekke', '340 Cedar Street', NULL, 'St. Paul', 'MN', '55101', '8882984723', 'A'); -- Create hotel building records INSERT INTO building (building_id, prop_id, build_name, build_floors, build_air, list_status) VALUES ('2000000001", 1000000001', 'Tower', 5, 'N', TA'); INSERT INTO building 1000000001', 'Science', 4, 'Y', 'I'); VALUES ('2000000002", INSERT INTO building '1000000002', 'Cedar Tower', 13, 'Y', 'A'); VALUES (*2000000004', '1000000002', 'Victory Plaza', 6, 'Y', 'A'); VALUES ('2000000003', INSERT INTO building -- Create hotel rooms for each building INSERT INTO room (room_id, building_id, room_num, room_type, room_smoking, room_rate, list_status) VALUES ('3000000001, 2000000001', 101', 'Accessible', N', '21.00', TA'); INSERT INTO room. '2000000001', '201', 'Queen', 'N', '21.00', 'A'); VALUES (*3000000002', INSERT INTO room. INSERT INTO room. VALUES ('3000000003', '2000000001', '301', 'Double', 'N', '20.00', 'A'); VALUES (*3000000004', '2000000001', '401', 'King', 'N', '22.00', 'A'); '2000000001', '501', 'King', 'N', '22.00', 'A'); 2000000002', '112', 'Accessible', 'Y', '21.00', 'I'); '2000000002', '222', 'Queen', 'Y', '22.00', 'I'); '2000000002', '332', 'Double', 'Y', '20.00', 'I'); 'King', 'Y', '22.00', 'I'); '2000000002', '442', '2000000002', '552', 'King', 'Y', '22.00', 'I'); '2000000003, 103', 'Accessible', 'N', '21.00', 'A'); '2000000003', '203', 'Queen', 'N', '21.00', 'A'); '2000000003', '303', 'Double', 'N', '20.00', 'A'); '2000000003', '403', 'King', 'N', '22.00', 'A'); 2000000003', '503', 'King', 'N', '22.00', 'A'); '2000000004', '114', 'Accessible', 'y', '21.00', 'A'); VALUES (*3000000017', '2000000004', '224', 'Queen', 'Y', '21.00', 'A') 2000000004', '334', 'Double', 'Y', '20.00', 'A'); 2000000004', '444', 'King', 'Y', '22.00', 'A'); VALUES (*3000000020', '2000000004', '554', 'King', 'y', '22.00', 'A'); INSERT INTO room VALUES (*3000000005', INSERT INTO room. VALUES ('3000000006", INSERT INTO room VALUES ('3000000007', INSERT INTO room. VALUES ('3000000008', INSERT INTO room. VALUES (*3000000009', INSERT INTO room. VALUES (*3000000010', INSERT INTO room. VALUES (*3000000011', INSERT INTO room. VALUES (*3000000012', INSERT INTO room. VALUES (*3000000013', INSERT INTO room. VALUES (*3000000014', INSERT INTO room. VALUES (*3000000015, INSERT INTO room. VALUES (*3000000016', INSERT INTO room. INSERT INTO room. VALUES ('3000000018', INSERT INTO room. VALUES ('3000000019", INSERT INTO room. -- Create guest records INSERT INTO guest (guest_id, guest_fname, guest_lname, guest_mi, guest_dob, guest_email, list_status) VALUES ('4000000001, Jen', 'Rosato', TK', '22-FEB-1983', 'jrosato@css.edu', 'A'); INSERT INTO guest VALUES ('4000000002', 'Bradon', 'Olson', 'P', '26-OCT-1983', 'bolson@css.edu', 'A'); INSERT INTO guest VALUES ('4000000003', 'David', 'Vosen', 'B', '8-JAN-1976', 'dvosen@css.edu', 'A'); INSERT INTO guest VALUES ('4000000004', 'Thomas', 'Buck', 'A', '19-APR-1983', 'tbuck2@css.edu', 'A'); INSERT INTO guest VALUES ('4000000005', 'Thomas', 'Gibbons', 'J', '20-JUL-1984', 'tgibbons@css.edu', 'A'); INSERT INTO guest VALUES ('4000000006', 'Kristopher', 'Glesener', 'W', '19-AUG-1982', 'kglesener@css.edu', 'A'); INSERT INTO guest VALUES ('4000000007', 'David', 'Dykshoorn', 'K', '19-SEP-1984', 'ddykshoorn@css.edu', 'A'); INSERT INTO guest VALUES ('4000000008', 'Richard', 'Caya', 'R', '27-AUG-1970', 'rcayal@css.edu', 'A'); INSERT INTO guest VALUES ('4000000009', 'Scott', 'Lee-Eichenwald', 'S', '27-FEB-1984', 'slee@css.edu', 'A'); INSERT INTO guest VALUES ('4000000010', 'Kolli', 'Srinivas', 'T', '14-SEP-1985', 'dyoung@css.edu', 'A'); -- Create guest_address records for guests INSERT INTO guest_address (address_id, guest_id, address_type, guest_addl, guest_add2, guest_city, guest_state, guest_zip, list_status) VALUES ('5000000001, 4000000001', Work', 'Main Campus', '550 3rd Avenue North', 'Duluth', 'MN', '55811', 'A'); INSERT INTO guest_address VALUES ('5000000002", 4000000001', 'Home', '124 1st Avenue East', 'Suite 2323', 'Duluth', 'MN', '55805', 'A'); INSERT INTO guest_address VALUES (*5000000003', '4000000002', 'Work', 'Main Campus', '550 3rd Avenue North', 'Duluth', 'MN', '55811', 'A'); INSERT INTO guest_address VALUES ('5000000004", 4000000003', 'Work', 'Main Campus', '550 3rd Avenue North', 'Duluth', 'MN', '55811', 'A'); INSERT INTO guest_address VALUES (*5000000005', '4000000004', 'Work', 'Main Campus', '550 3rd Avenue North', 'Duluth', 'MN', '55811', 'A'); INSERT INTO guest_address VALUES ('5000000006, 4000000005', 'Work', 'Main Campus', '550 3rd Avenue North', 'Duluth', 'MN', '55811', 'A'); INSERT INTO guest_address VALUES ('5000000007' 'MN', '55811', 'A'); INSERT INTO guest_address 4000000006', 'Work', 'Main Campus', '550 3rd Avenue North', 'Duluth', VALUES ('5000000008", 4000000007', 'Work', 'Main Campus', '550 3rd Avenue North', 'Duluth', 'MN', '55811', 'A'); INSERT INTO guest_address VALUES ('5000000009", 4000000008', 'Work', 'Main Campus', '550 3rd Avenue North', 'Duluth', 'MN', '55811', 'A'); INSERT INTO guest_address VALUES ('5000000010", 4000000008', 'Home', '12342 Fairview Street', NULL, 'St. Paul', 'MN', '55101', 'I'); INSERT INTO guest_address VALUES ('5000000011', 4000000008', 'Home', '12334 Ferndale Avenue', Null, 'St. Paul', 'MN', '55101', 'A'); INSERT INTO guest_address VALUES ('5000000012", 4000000009', 'Work', 'Main Campus', '550 3rd Avenue North', 'Duluth', 'MN', '55811', 'A'); INSERT INTO guest_address VALUES ('5000000013', 4000000010', 'Work', 'Main Campus', '550 3rd Avenue North', 'Duluth', 'MN', '55811', 'A'); -- Create phone records for guests INSERT INTO phone (phone_id, guest_id, phone_type, phone_num, list_status) VALUES ('6000000001, 4000000001', Work', '8004475444", TA'); INSERT INTO phone VALUES ('6000000002", 4000000002', 'Work', '8004475444', 'A'); INSERT INTO phone VALUES ('6000000003', '4000000003', 'Work', '8004475444', 'A'); INSERT INTO phone VALUES ('6000000004', '4000000004', 'Work', '8004475444', 'A'); INSERT INTO phone VALUES ('6000000005', '4000000005', 'Work', '8004475444', 'A'); INSERT INTO phone VALUES ('6000000006", 4000000006', 'Work', '8004475444', 'A'); INSERT INTO phone VALUES ('6000000007', '4000000007', 'Work', '8004475444', 'A'); INSERT INTO phone VALUES ('6000000008", 4000000008', 'Work', '8004475444', 'A') INSERT INTO phone. VALUES ('6000000009", 4000000008', 'Home', *6512981015', 'I'); INSERT INTO phone VALUES ('6000000010', '4000000008', 'Home', *6512981015', 'A'); Q + INSERT INTO phone VALUES ('6000000011', 4000000009', 'Work', 8004475444", 'A'); VALUES ('6000000012', '4000000010', 'Work', '8004475444', 'A'); INSERT INTO phone -- Create reservation records. INSERT INTO reservation (reservation_id, guest_id, res_type, res_date, checkin_date, checkout_date, cancelled_date, cancel_fee, created_date, created_by, modified_date, modified_by) VALUES (*7000000001", 4000000001','Confirmed', T09-SEP-2019 10-JAN-2020 11-MAY-2020", NULL, NULL, '09-SEP-2019', 'DVOSEN", NULL, NULL); INSERT INTO reservation VALUES (*7000000002", 4000000003', 'Confirmed', '09-SEP-2019', '10-JAN-2020', '11-MAY-2020", NULL, NULL, '09-SEP-2019', 'DVOSEN", NULL, NULL); INSERT INTO reservation VALUES ('7000000003", 4000000005', 'Confirmed', '09-SEP-2019', '10-JAN-2020', '11-MAY-2020", NULL, NULL, '09-SEP-2019', 'DVOSEN', NULL, NULL); INSERT INTO reservation VALUES ('7000000004', 4000000007', 'Confirmed', '09-SEP-2019', '10-JAN-2020', '11-MAY-2020", NULL, NULL, '09-SEP-2019', 'DVOSEN, NULL, NULL); INSERT INTO reservation. VALUES (*7000000005', '4000000009', 'Confirmed', '09-SEP-2019', '10-JAN-2020', '11-MAY-2020", NULL, NULL, '09-SEP-2019', 'DVOSEN', NULL, NULL); INSERT INTO reservation VALUES ('7000000006", 4000000002', 'Confirmed', '09-SEP-2019', '10-JAN-2020', '11-MAY-2020", NULL, NULL, '09-SEP-2019', 'DVOSEN, NULL, NULL); INSERT INTO reservation VALUES ('7000000007", 4000000004', 'Confirmed', '09-SEP-2019', '10-JAN-2020', '11-MAY-2020", NULL, NULL, '09-SEP-2019', 'DVOSEN", NULL, NULL); INSERT INTO reservation VALUES ('7000000008", 4000000006', 'Confirmed', '09-SEP-2019', '10-JAN-2020', '11-MAY-2020", NULL, NULL, '09-SEP-2019', 'DVOSEN", NULL, NULL); INSERT INTO reservation. VALUES (*7000000009', '4000000008', 'Confirmed', '09-SEP-2019', '10-JAN-2020', '11-MAY-2020", NULL, NULL, '09-SEP-2019', 'DVOSEN', NULL, NULL); INSERT INTO reservation VALUES ('7000000010, 4000000010', 'Confirmed', '09-SEP-2019', '10-JAN-2020', '11-MAY-2020", NULL, NULL, '09-SEP-2019', 'DVOSEN", NULL, NULL); INSERT INTO reservation VALUES ('7000000011, 4000000001', 'Confirmed', '31-OCT-2019', '15-MAY-2020', '23-AUG-2020", NULL, NULL, 31-OCT-2019', 'DVOSEN', NULL, NULL); INSERT INTO reservation VALUES ('7000000012", 4000000003', 'Confirmed', '31-OCT-2019', '15-MAY-2020', '23-AUG-2020", NULL, NULL, '31-OCT-2019', 'DVOSEN, NULL, NULL); INSERT INTO reservation VALUES (*7000000013', '4000000005', 'Confirmed', '31-OCT-2019', '15-MAY-2020', '23-AUG-2020", NULL, NULL, 31-OCT-2019', 'DVOSEN", NULL, NULL); INSERT INTO reservation VALUES ('7000000014", 4000000007', 'Confirmed', '31-OCT-2019', '15-MAY-2020', '23-AUG-2020", NULL, NULL, '31-OCT-2019', 'DVOSEN, NULL, NULL); INSERT INTO reservation VALUES ('7000000015', '4000000009', 'Confirmed', '31-OCT-2019', '15-MAY-2020', '23-AUG-2020", NULL, NULL, '31-OCT-2019', 'DVOSEN', NULL, NULL); INSERT INTO reservation VALUES ('7000000016", 4000000002', 'Cancelled', '31-OCT-2019, NULL, NULL, '17-JUL-2019', 25.00, 31-OCT-2019', 'DVOSEN', NULL, NULL); INSERT INTO reservation VALUES (*7000000017', '4000000004', 'Confirmed', '31-OCT-2019', '15-MAY-2020', '23-AUG-2020", NULL, NULL, '31-OCT-2019', 'DVOSEN, NULL, NULL); INSERT INTO reservation VALUES ('7000000018', '4000000006', 'Confirmed', '31-OCT-2019', '15-MAY-2020', '23-AUG-2020", NULL, NULL, 31-OCT-2019', 'DVOSEN', NULL, NULL); INSERT INTO reservation VALUES ('7000000019', '4000000008', 'Confirmed', '31-OCT-2019', '15-MAY-2020', '23-AUG-2020", NULL, NULL, '31-OCT-2019', 'DVOSEN, NULL, NULL); INSERT INTO reservation VALUES ('7000000020", '4000000010', 'Confirmed', '31-OCT-2019', '15-MAY-2020', '23-AUG-2020", NULL, NULL, '31-OCT-2019', 'DVOSEN', NULL, NULL) ; (reservation_id, room_id, res_rate, list_status) '3000000001, 2627.00, 'A'); 3000000002', 2627.00, 'A'); '3000000003, 2504.00, 'A'); '3000000004', 2750.00, 'A'); '3000000005', 2750.00, 'A'); INSERT INTO room_reservation VALUES ('7000000001', INSERT INTO room_reservation VALUES (7000000002", INSERT INTO room_reservation VALUES ('7000000003', INSERT INTO room_reservation VALUES ('7000000004', INSERT INTO room_reservation VALUES ('7000000005', INSERT INTO room_reservation VALUES ('7000000006', INSERT INTO room_reservation VALUES ('7000000006', INSERT INTO room_reservation VALUES ('7000000007', INSERT INTO room_reservation VALUES ('7000000008", INSERT INTO room_reservation VALUES ('7000000008', INSERT INTO room_reservation VALUES ('7000000009', INSERT INTO room_reservation 3000000006, 2627.00, 'A'); '3000000007, 2750.00, 'A'); '3000000008, 2504.00, 'A'); 3000000009, 2750.00, 'A'); '3000000010, 2750.00, 'A'); '3000000011', 2627.00, 'A'); VALUES ('7000000010', '3000000012, 2627.00, 'A'); INSERT INTO room_reservation VALUES ('7000000010', '3000000013', 2504.00, 'A'); INSERT INTO room_reservation VALUES ('7000000011', '3000000012, 2157.00, 'A'); INSERT INTO room_reservation VALUES (7000000012', '3000000001, 2157.00, 'A'); INSERT INTO room_reservation VALUES ('7000000013', '3000000002, 215700, 'A'); INSERT INTO room_reservation VALUES ('7000000013', '3000000003, 2157.00, 'A'); INSERT INTO room_reservation VALUES ('7000000013', '3000000013', 2280.00, 'A'); INSERT INTO room_reservation VALUES ('7000000014', '3000000014, 2280.00, 'A'); INSERT INTO room_reservation VALUES ('7000000015, 3000000015, 2280.00, 'A'); INSERT INTO room_reservation VALUES ('7000000016', 3000000016', 2157.00, 'A'); INSERT INTO room_reservation VALUES ('7000000017', '3000000017, 2157.00, 'A'); INSERT INTO room_reservation VALUES ('7000000018, 3000000018, 2034.00, 'A'); INSERT INTO room_reservation VALUES ('7000000019', '3000000019, 2280.00, 'A'); INSERT INTO room_reservation VALUES ('7000000020', '3000000020, 2157.00, 'A'); -- Create list values INSERT INTO lov (list_id, list_name, list_display, list_value, list_status) VALUES ('9000000001", TADDRESS TYPE', 'HOME', INSERT INTO lov VALUES ('9000000002', 'ADDRESS TYPE', 'Work', 'WORK', 'A'); 'ADDRESS TYPE", 'Other', 'OTHER', INSERT INTO lov VALUES ('9000000003', INSERT INTO lov VALUES (*9000000004', INSERT INTO lov VALUES ('9000000005', INSERT INTO lov VALUES ('9000000006", INSERT INTO lov VALUES ('9000000007', INSERT INTO lov VALUES ('9000000008", 'PHONE TYPE', 'Home', 'HOME', 'A'); 'PHONE TYPE', 'Work', 'WORK', 'A'); 'PHONE TYPE', 'Mobile', 'OTHER', 'A'); 'RESERVATION TYPE', 'Confirmed', 'CONFIRMED', 'A'); 'RESERVATION TYPE', 'Cancelled', 'CANCELLED', 'A'); /*--1) View the first name, last name, and the number of reservations for all active guests, displayed as "Reservations", even if a guest does not have a reservation, and sort the results alphabetically by last name and first name. SELECT -- use COUNT (reservation_id) as an attribute here FROM LEFT OUTER JOIN SELECT --3) --2) View the property name, building name, property city, property state for all building names containing the word "Tower" or "Plaza". Hint: use LIKE -- -- -- -- View the property name, property city, property state, average base rate for all active rooms in the property (displayed as "Property Average"), and the average base rate for active rooms across the entire chain (this list/attribute subquery is to be displayed as "Chain Average") for only those properties with a "Property Average" greater than or equal to "Chain Average" HINT: use two INNER JOIN statements for building and room tables and reuse your same subquery statement from before. SELECT --AVG (r.room_rate) AS Property Average', \ --5) GROUP BY ORDER BY ON AND table.list_status = 'A' -- -- Unit 11 Lab Starter File This script has comment and script structure to help solve Unit 11 Lab Created by: David Vosen Created on: 11/18/2016 Modifiied on: 11/15/2019 Modified by: Student Name Modified on: Current Date ========= -- FROM building b INNER JOIN property p ON WHERE --field LIKE '*Tower' OR OR --4) -- HINT: Use DATEDIFF function Unit 11 Exercise Part 2 #2 example View the reservation_id and total amount due for each reservation (that has not been cancelled). You will need to calculate this Total Due' based on the number of nights guests are staying multiplied by rate specified for each room. HINT: Use a single INNER JOIN here like #3 but without the subquery and HAVING elements and can reuse parts of #4 SELECT - Use a SUM (DATEDIFF for days (dd) and multiple times room_rate FROM ====== (SELECT FROM room) AS 'Chain Average' FROM property p INNER JOIN building b ON PK = FK INNER JOIN room r ON PK= FK WHERE r.list_status = 'A' GROUP BY all fields listed in 1st SELECT other than AVG & SubQuery HAVING --reuse the 'Property Average' alias to be greater than and equal -- to 'Chain Average' (Subquery alias) View the number of nights guests are staying for each reservation WHERE the reservation is not cancelled (is NULL) JOIN JOIN WHERE GROUP BY */ ON ON ; --JOIN reservation and room_reservation - ON their PK to FK -- JOIN reservation_reservation and room -- ON their PK to FK -- where no cancelations -- fields beyond the aggregate in listed SQL Script for Unit 11 Lab (Hotel Scholastica Reservation Database Records) This script will drop and then re-create the tables for a Hotel database and add data to each table. Created by: David Vosen Created on: 11/14/2017 Modified by: David Vosen Modified on: 11/02/2018 Modified on: 11/14/2019 Modified on: 07/25/2020 Modified by: Student Name Modified on: Current Date -- DROP all the past Bookstore Database Tables IF OBJECT_ID ("payment') IF OBJECT_ID ('list') IF OBJECT ID ('line')) IF OBJECT ID ('invoice') IF OBJECT ID ('product') IF OBJECT_ID ('vendor') IF OBJECT_ID ("customer') IF OBJECT_ID IF OBJECT ID IF OBJECT_ID ("room") IF OBJECT_ID ("building')) IF OBJECT_ID ('property') -- DROP all tables for previous Hotel Reservation Database IF OBJECT_ID ("lov") IF OBJECT ID ('room_reservation') ("reservation') ('phone') IF OBJECT_ID ('guest_address') IF OBJECT ID ("guest") -- Create the Property table CREATE TABLE property ( prop_id prop_name prop_mgr prop_add1 prop_add2 prop_city prop_state prop_zip prop_phone list_status PRIMARY KEY (prop_id)); -- Create the building CREATE TABLE building ( building_id prop_id build_name build floors build air list_status table room_num room_type room_smoking room_rate list_status VARCHAR (10) VARCHAR (30), VARCHAR (40), VARCHAR (25), VARCHAR (25), VARCHAR (25), CHAR (2), VARCHAR(9), CHAR(10), CHAR (1) PRIMARY KEY (building_id), FOREIGN KEY (prop_id) -- Create the room table CREATE TABLE room ( room id building_id PRIMARY KEY (room_id), FOREIGN KEY (building_id) IS NOT NULL IS NOT NULL IS NOT NULL IS NOT NULL IS NOT NULL IS NOT NULL IS NOT NULL VARCHAR (10) VARCHAR (10) VARCHAR (25), INT, CHAR (1), IS NOT NULL IS NOT NULL DROP TABLE list; DROP TABLE line; DROP TABLE invoice; DROP TABLE product; DROP TABLE vendor; DROP TABLE customer; NOT NULL CHAR (1), CHAR (1) REFERENCES property); IS NOT NULL IS NOT NULL NOT NULL VARCHAR (10) VARCHAR (10) INT, VARCHAR (15), IS NOT NULL NOT NULL NOT NULL, IS NOT NULL IS NOT NULL IS NOT NULL IS NOT NULL DROP TABLE payment; NOT NULL MONEY, CHAR (1) REFERENCES building); NOT NULL UNIQUE, NOT NULL NOT NULL, DEFAULT 'A', UNIQUE, "/ DROP TABLE lov; DROP TABLE room_reservation; DROP TABLE reservation; DROP TABLE phone; DROP TABLE guest_address; DROP TABLE guest; DROP TABLE room; DROP TABLE building; DROP TABLE property; DEFAULT 'A', UNIQUE, DEFAULT 'A', -- Create the Guest table CREATE TABLE guest ( guest_id guest_fname guest iname guest_mi guest_dob guest_email list status PRIMARY KEY (guest_id)); -- Create the Address table CREATE TABLE guest_address ( address_id guest_id PRIMARY KEY (address_id), FOREIGN KEY (guest_id) PRIMARY KEY (phone_id), FOREIGN KEY (guest_id) VARCHAR (10) VARCHAR (20), VARCHAR (20), address_type VARCHAR (15), guest_addl VARCHAR (25), guest_add2 VARCHAR (25), guest_city guest_state guest_zip list_status CHAR (1), DATETIME, VARCHAR (100), CHAR (1) -- Create the Phone table CREATE TABLE phone ( phone_id guest_id phone_type phone_num list_status reservation_id guest_id res_type res_date -- -- Create table reservation CREATE TABLE reservation ( room_id res_rate list_status VARCHAR (10) VARCHAR (10) VARCHAR (25), CHAR(2), CHAR (1) VARCHAR (9), REFERENCES guest); VARCHAR (10) VARCHAR (10) VARCHAR (15), CHAR(10), CHAR (1) REFERENCES guest); VARCHAR (10) NOT NULL NOT NULL MONEY, checkin_date DATETIME, checkout_date DATETIME, cancelled_date DATETIME, cancel_fee created_date DATETIME, created by modified_date DATETIME, modified by PRIMARY KEY (reservation_id), FOREIGN KEY (guest_id) REFERENCES guest); VARCHAR (10) VARCHAR (15), DATETIME, reservation_id VARCHAR (10) -- Create table room_reservation bridge table CREATE TABLE room_reservation ( PRIMARY KEY (reservation_id, room_id), FOREIGN KEY (reservation_id) FOREIGN KEY (room_id) NOT NULL NOT NULL, NOT NULL VARCHAR(10), VARCHAR (10), -- Create the list of values table CREATE TABLE lov ( VARCHAR (10) MONEY, CHAR (1) VARCHAR (10) VARCHAR (20), NOT NULL NOT NULL, NOT NULL, NOT NULL VARCHAR (20), CHAR (1) UNIQUE, REFERENCES reservation, REFERENCES room); DEFAULT 'A' NOT NULL NOT NULL, UNIQUE, NOT NULL NOT NULL, UNIQUE, NOT NULL list_id list_name list_display VARCHAR (20), list_value list_status PRIMARY KEY (list_id)); With the tables created, the script now populates each table NOT NULL UNIQUE, DEFAULT 'A', DEFAULT 'A', DEFAULT 'A', UNIQUE, DEFAULT 'A', -- Create hotel property records INSERT INTO property (prop_id, prop_name, prop_mgr, prop_addl, prop_add2, prop_city, prop_state, prop_zip, prop_phone, list_status) VALUES (*1000000001', 'Hotel Scholastica - Duluth', 'Dr. Barbara McDonald', '1200 Kenwood Avenue', NULL, 'Duluth', 'MN', '55811', '8004475444', 'A'); INSERT INTO property (prop_id, prop_name, prop_mgr, prop_addl, prop_add2, prop_city, prop_state, prop_zip, prop_phone, list_status) VALUES ('1000000002", 'Hotel Scholastica - St. Paul', 'Thomas Brekke', '340 Cedar Street', NULL, 'St. Paul', 'MN', '55101', '8882984723', 'A'); -- Create hotel building records INSERT INTO building (building_id, prop_id, build_name, build_floors, build_air, list_status) VALUES ('2000000001", 1000000001', 'Tower', 5, 'N', TA'); INSERT INTO building 1000000001', 'Science', 4, 'Y', 'I'); VALUES ('2000000002", INSERT INTO building '1000000002', 'Cedar Tower', 13, 'Y', 'A'); VALUES (*2000000004', '1000000002', 'Victory Plaza', 6, 'Y', 'A'); VALUES ('2000000003', INSERT INTO building -- Create hotel rooms for each building INSERT INTO room (room_id, building_id, room_num, room_type, room_smoking, room_rate, list_status) VALUES ('3000000001, 2000000001', 101', 'Accessible', N', '21.00', TA'); INSERT INTO room. '2000000001', '201', 'Queen', 'N', '21.00', 'A'); VALUES (*3000000002', INSERT INTO room. INSERT INTO room. VALUES ('3000000003', '2000000001', '301', 'Double', 'N', '20.00', 'A'); VALUES (*3000000004', '2000000001', '401', 'King', 'N', '22.00', 'A'); '2000000001', '501', 'King', 'N', '22.00', 'A'); 2000000002', '112', 'Accessible', 'Y', '21.00', 'I'); '2000000002', '222', 'Queen', 'Y', '22.00', 'I'); '2000000002', '332', 'Double', 'Y', '20.00', 'I'); 'King', 'Y', '22.00', 'I'); '2000000002', '442', '2000000002', '552', 'King', 'Y', '22.00', 'I'); '2000000003, 103', 'Accessible', 'N', '21.00', 'A'); '2000000003', '203', 'Queen', 'N', '21.00', 'A'); '2000000003', '303', 'Double', 'N', '20.00', 'A'); '2000000003', '403', 'King', 'N', '22.00', 'A'); 2000000003', '503', 'King', 'N', '22.00', 'A'); '2000000004', '114', 'Accessible', 'y', '21.00', 'A'); VALUES (*3000000017', '2000000004', '224', 'Queen', 'Y', '21.00', 'A') 2000000004', '334', 'Double', 'Y', '20.00', 'A'); 2000000004', '444', 'King', 'Y', '22.00', 'A'); VALUES (*3000000020', '2000000004', '554', 'King', 'y', '22.00', 'A'); INSERT INTO room VALUES (*3000000005', INSERT INTO room. VALUES ('3000000006", INSERT INTO room VALUES ('3000000007', INSERT INTO room. VALUES ('3000000008', INSERT INTO room. VALUES (*3000000009', INSERT INTO room. VALUES (*3000000010', INSERT INTO room. VALUES (*3000000011', INSERT INTO room. VALUES (*3000000012', INSERT INTO room. VALUES (*3000000013', INSERT INTO room. VALUES (*3000000014', INSERT INTO room. VALUES (*3000000015, INSERT INTO room. VALUES (*3000000016', INSERT INTO room. INSERT INTO room. VALUES ('3000000018', INSERT INTO room. VALUES ('3000000019", INSERT INTO room. -- Create guest records INSERT INTO guest (guest_id, guest_fname, guest_lname, guest_mi, guest_dob, guest_email, list_status) VALUES ('4000000001, Jen', 'Rosato', TK', '22-FEB-1983', 'jrosato@css.edu', 'A'); INSERT INTO guest VALUES ('4000000002', 'Bradon', 'Olson', 'P', '26-OCT-1983', 'bolson@css.edu', 'A'); INSERT INTO guest VALUES ('4000000003', 'David', 'Vosen', 'B', '8-JAN-1976', 'dvosen@css.edu', 'A'); INSERT INTO guest VALUES ('4000000004', 'Thomas', 'Buck', 'A', '19-APR-1983', 'tbuck2@css.edu', 'A'); INSERT INTO guest VALUES ('4000000005', 'Thomas', 'Gibbons', 'J', '20-JUL-1984', 'tgibbons@css.edu', 'A'); INSERT INTO guest VALUES ('4000000006', 'Kristopher', 'Glesener', 'W', '19-AUG-1982', 'kglesener@css.edu', 'A'); INSERT INTO guest VALUES ('4000000007', 'David', 'Dykshoorn', 'K', '19-SEP-1984', 'ddykshoorn@css.edu', 'A'); INSERT INTO guest VALUES ('4000000008', 'Richard', 'Caya', 'R', '27-AUG-1970', 'rcayal@css.edu', 'A'); INSERT INTO guest VALUES ('4000000009', 'Scott', 'Lee-Eichenwald', 'S', '27-FEB-1984', 'slee@css.edu', 'A'); INSERT INTO guest VALUES ('4000000010', 'Kolli', 'Srinivas', 'T', '14-SEP-1985', 'dyoung@css.edu', 'A'); -- Create guest_address records for guests INSERT INTO guest_address (address_id, guest_id, address_type, guest_addl, guest_add2, guest_city, guest_state, guest_zip, list_status) VALUES ('5000000001, 4000000001', Work', 'Main Campus', '550 3rd Avenue North', 'Duluth', 'MN', '55811', 'A'); INSERT INTO guest_address VALUES ('5000000002", 4000000001', 'Home', '124 1st Avenue East', 'Suite 2323', 'Duluth', 'MN', '55805', 'A'); INSERT INTO guest_address VALUES (*5000000003', '4000000002', 'Work', 'Main Campus', '550 3rd Avenue North', 'Duluth', 'MN', '55811', 'A'); INSERT INTO guest_address VALUES ('5000000004", 4000000003', 'Work', 'Main Campus', '550 3rd Avenue North', 'Duluth', 'MN', '55811', 'A'); INSERT INTO guest_address VALUES (*5000000005', '4000000004', 'Work', 'Main Campus', '550 3rd Avenue North', 'Duluth', 'MN', '55811', 'A'); INSERT INTO guest_address VALUES ('5000000006, 4000000005', 'Work', 'Main Campus', '550 3rd Avenue North', 'Duluth', 'MN', '55811', 'A'); INSERT INTO guest_address VALUES ('5000000007' 'MN', '55811', 'A'); INSERT INTO guest_address 4000000006', 'Work', 'Main Campus', '550 3rd Avenue North', 'Duluth', VALUES ('5000000008", 4000000007', 'Work', 'Main Campus', '550 3rd Avenue North', 'Duluth', 'MN', '55811', 'A'); INSERT INTO guest_address VALUES ('5000000009", 4000000008', 'Work', 'Main Campus', '550 3rd Avenue North', 'Duluth', 'MN', '55811', 'A'); INSERT INTO guest_address VALUES ('5000000010", 4000000008', 'Home', '12342 Fairview Street', NULL, 'St. Paul', 'MN', '55101', 'I'); INSERT INTO guest_address VALUES ('5000000011', 4000000008', 'Home', '12334 Ferndale Avenue', Null, 'St. Paul', 'MN', '55101', 'A'); INSERT INTO guest_address VALUES ('5000000012", 4000000009', 'Work', 'Main Campus', '550 3rd Avenue North', 'Duluth', 'MN', '55811', 'A'); INSERT INTO guest_address VALUES ('5000000013', 4000000010', 'Work', 'Main Campus', '550 3rd Avenue North', 'Duluth', 'MN', '55811', 'A'); -- Create phone records for guests INSERT INTO phone (phone_id, guest_id, phone_type, phone_num, list_status) VALUES ('6000000001, 4000000001', Work', '8004475444", TA'); INSERT INTO phone VALUES ('6000000002", 4000000002', 'Work', '8004475444', 'A'); INSERT INTO phone VALUES ('6000000003', '4000000003', 'Work', '8004475444', 'A'); INSERT INTO phone VALUES ('6000000004', '4000000004', 'Work', '8004475444', 'A'); INSERT INTO phone VALUES ('6000000005', '4000000005', 'Work', '8004475444', 'A'); INSERT INTO phone VALUES ('6000000006", 4000000006', 'Work', '8004475444', 'A'); INSERT INTO phone VALUES ('6000000007', '4000000007', 'Work', '8004475444', 'A'); INSERT INTO phone VALUES ('6000000008", 4000000008', 'Work', '8004475444', 'A') INSERT INTO phone. VALUES ('6000000009", 4000000008', 'Home', *6512981015', 'I'); INSERT INTO phone VALUES ('6000000010', '4000000008', 'Home', *6512981015', 'A'); Q + INSERT INTO phone VALUES ('6000000011', 4000000009', 'Work', 8004475444", 'A'); VALUES ('6000000012', '4000000010', 'Work', '8004475444', 'A'); INSERT INTO phone -- Create reservation records. INSERT INTO reservation (reservation_id, guest_id, res_type, res_date, checkin_date, checkout_date, cancelled_date, cancel_fee, created_date, created_by, modified_date, modified_by) VALUES (*7000000001", 4000000001','Confirmed', T09-SEP-2019 10-JAN-2020 11-MAY-2020", NULL, NULL, '09-SEP-2019', 'DVOSEN", NULL, NULL); INSERT INTO reservation VALUES (*7000000002", 4000000003', 'Confirmed', '09-SEP-2019', '10-JAN-2020', '11-MAY-2020", NULL, NULL, '09-SEP-2019', 'DVOSEN", NULL, NULL); INSERT INTO reservation VALUES ('7000000003", 4000000005', 'Confirmed', '09-SEP-2019', '10-JAN-2020', '11-MAY-2020", NULL, NULL, '09-SEP-2019', 'DVOSEN', NULL, NULL); INSERT INTO reservation VALUES ('7000000004', 4000000007', 'Confirmed', '09-SEP-2019', '10-JAN-2020', '11-MAY-2020", NULL, NULL, '09-SEP-2019', 'DVOSEN, NULL, NULL); INSERT INTO reservation. VALUES (*7000000005', '4000000009', 'Confirmed', '09-SEP-2019', '10-JAN-2020', '11-MAY-2020", NULL, NULL, '09-SEP-2019', 'DVOSEN', NULL, NULL); INSERT INTO reservation VALUES ('7000000006", 4000000002', 'Confirmed', '09-SEP-2019', '10-JAN-2020', '11-MAY-2020", NULL, NULL, '09-SEP-2019', 'DVOSEN, NULL, NULL); INSERT INTO reservation VALUES ('7000000007", 4000000004', 'Confirmed', '09-SEP-2019', '10-JAN-2020', '11-MAY-2020", NULL, NULL, '09-SEP-2019', 'DVOSEN", NULL, NULL); INSERT INTO reservation VALUES ('7000000008", 4000000006', 'Confirmed', '09-SEP-2019', '10-JAN-2020', '11-MAY-2020", NULL, NULL, '09-SEP-2019', 'DVOSEN", NULL, NULL); INSERT INTO reservation. VALUES (*7000000009', '4000000008', 'Confirmed', '09-SEP-2019', '10-JAN-2020', '11-MAY-2020", NULL, NULL, '09-SEP-2019', 'DVOSEN', NULL, NULL); INSERT INTO reservation VALUES ('7000000010, 4000000010', 'Confirmed', '09-SEP-2019', '10-JAN-2020', '11-MAY-2020", NULL, NULL, '09-SEP-2019', 'DVOSEN", NULL, NULL); INSERT INTO reservation VALUES ('7000000011, 4000000001', 'Confirmed', '31-OCT-2019', '15-MAY-2020', '23-AUG-2020", NULL, NULL, 31-OCT-2019', 'DVOSEN', NULL, NULL); INSERT INTO reservation VALUES ('7000000012", 4000000003', 'Confirmed', '31-OCT-2019', '15-MAY-2020', '23-AUG-2020", NULL, NULL, '31-OCT-2019', 'DVOSEN, NULL, NULL); INSERT INTO reservation VALUES (*7000000013', '4000000005', 'Confirmed', '31-OCT-2019', '15-MAY-2020', '23-AUG-2020", NULL, NULL, 31-OCT-2019', 'DVOSEN", NULL, NULL); INSERT INTO reservation VALUES ('7000000014", 4000000007', 'Confirmed', '31-OCT-2019', '15-MAY-2020', '23-AUG-2020", NULL, NULL, '31-OCT-2019', 'DVOSEN, NULL, NULL); INSERT INTO reservation VALUES ('7000000015', '4000000009', 'Confirmed', '31-OCT-2019', '15-MAY-2020', '23-AUG-2020", NULL, NULL, '31-OCT-2019', 'DVOSEN', NULL, NULL); INSERT INTO reservation VALUES ('7000000016", 4000000002', 'Cancelled', '31-OCT-2019, NULL, NULL, '17-JUL-2019', 25.00, 31-OCT-2019', 'DVOSEN', NULL, NULL); INSERT INTO reservation VALUES (*7000000017', '4000000004', 'Confirmed', '31-OCT-2019', '15-MAY-2020', '23-AUG-2020", NULL, NULL, '31-OCT-2019', 'DVOSEN, NULL, NULL); INSERT INTO reservation VALUES ('7000000018', '4000000006', 'Confirmed', '31-OCT-2019', '15-MAY-2020', '23-AUG-2020", NULL, NULL, 31-OCT-2019', 'DVOSEN', NULL, NULL); INSERT INTO reservation VALUES ('7000000019', '4000000008', 'Confirmed', '31-OCT-2019', '15-MAY-2020', '23-AUG-2020", NULL, NULL, '31-OCT-2019', 'DVOSEN, NULL, NULL); INSERT INTO reservation VALUES ('7000000020", '4000000010', 'Confirmed', '31-OCT-2019', '15-MAY-2020', '23-AUG-2020", NULL, NULL, '31-OCT-2019', 'DVOSEN', NULL, NULL) ; (reservation_id, room_id, res_rate, list_status) '3000000001, 2627.00, 'A'); 3000000002', 2627.00, 'A'); '3000000003, 2504.00, 'A'); '3000000004', 2750.00, 'A'); '3000000005', 2750.00, 'A'); INSERT INTO room_reservation VALUES ('7000000001', INSERT INTO room_reservation VALUES (7000000002", INSERT INTO room_reservation VALUES ('7000000003', INSERT INTO room_reservation VALUES ('7000000004', INSERT INTO room_reservation VALUES ('7000000005', INSERT INTO room_reservation VALUES ('7000000006', INSERT INTO room_reservation VALUES ('7000000006', INSERT INTO room_reservation VALUES ('7000000007', INSERT INTO room_reservation VALUES ('7000000008", INSERT INTO room_reservation VALUES ('7000000008', INSERT INTO room_reservation VALUES ('7000000009', INSERT INTO room_reservation 3000000006, 2627.00, 'A'); '3000000007, 2750.00, 'A'); '3000000008, 2504.00, 'A'); 3000000009, 2750.00, 'A'); '3000000010, 2750.00, 'A'); '3000000011', 2627.00, 'A'); VALUES ('7000000010', '3000000012, 2627.00, 'A'); INSERT INTO room_reservation VALUES ('7000000010', '3000000013', 2504.00, 'A'); INSERT INTO room_reservation VALUES ('7000000011', '3000000012, 2157.00, 'A'); INSERT INTO room_reservation VALUES (7000000012', '3000000001, 2157.00, 'A'); INSERT INTO room_reservation VALUES ('7000000013', '3000000002, 215700, 'A'); INSERT INTO room_reservation VALUES ('7000000013', '3000000003, 2157.00, 'A'); INSERT INTO room_reservation VALUES ('7000000013', '3000000013', 2280.00, 'A'); INSERT INTO room_reservation VALUES ('7000000014', '3000000014, 2280.00, 'A'); INSERT INTO room_reservation VALUES ('7000000015, 3000000015, 2280.00, 'A'); INSERT INTO room_reservation VALUES ('7000000016', 3000000016', 2157.00, 'A'); INSERT INTO room_reservation VALUES ('7000000017', '3000000017, 2157.00, 'A'); INSERT INTO room_reservation VALUES ('7000000018, 3000000018, 2034.00, 'A'); INSERT INTO room_reservation VALUES ('7000000019', '3000000019, 2280.00, 'A'); INSERT INTO room_reservation VALUES ('7000000020', '3000000020, 2157.00, 'A'); -- Create list values INSERT INTO lov (list_id, list_name, list_display, list_value, list_status) VALUES ('9000000001", TADDRESS TYPE', 'HOME', INSERT INTO lov VALUES ('9000000002', 'ADDRESS TYPE', 'Work', 'WORK', 'A'); 'ADDRESS TYPE", 'Other', 'OTHER', INSERT INTO lov VALUES ('9000000003', INSERT INTO lov VALUES (*9000000004', INSERT INTO lov VALUES ('9000000005', INSERT INTO lov VALUES ('9000000006", INSERT INTO lov VALUES ('9000000007', INSERT INTO lov VALUES ('9000000008", 'PHONE TYPE', 'Home', 'HOME', 'A'); 'PHONE TYPE', 'Work', 'WORK', 'A'); 'PHONE TYPE', 'Mobile', 'OTHER', 'A'); 'RESERVATION TYPE', 'Confirmed', 'CONFIRMED', 'A'); 'RESERVATION TYPE', 'Cancelled', 'CANCELLED', 'A'); /*--1) View the first name, last name, and the number of reservations for all active guests, displayed as "Reservations", even if a guest does not have a reservation, and sort the results alphabetically by last name and first name. SELECT -- use COUNT (reservation_id) as an attribute here FROM LEFT OUTER JOIN SELECT --3) --2) View the property name, building name, property city, property state for all building names containing the word "Tower" or "Plaza". Hint: use LIKE -- -- -- -- View the property name, property city, property state, average base rate for all active rooms in the property (displayed as "Property Average"), and the average base rate for active rooms across the entire chain (this list/attribute subquery is to be displayed as "Chain Average") for only those properties with a "Property Average" greater than or equal to "Chain Average" HINT: use two INNER JOIN statements for building and room tables and reuse your same subquery statement from before. SELECT --AVG (r.room_rate) AS Property Average', \ --5) GROUP BY ORDER BY ON AND table.list_status = 'A' -- -- Unit 11 Lab Starter File This script has comment and script structure to help solve Unit 11 Lab Created by: David Vosen Created on: 11/18/2016 Modifiied on: 11/15/2019 Modified by: Student Name Modified on: Current Date ========= -- FROM building b INNER JOIN property p ON WHERE --field LIKE '*Tower' OR OR --4) -- HINT: Use DATEDIFF function Unit 11 Exercise Part 2 #2 example View the reservation_id and total amount due for each reservation (that has not been cancelled). You will need to calculate this Total Due' based on the number of nights guests are staying multiplied by rate specified for each room. HINT: Use a single INNER JOIN here like #3 but without the subquery and HAVING elements and can reuse parts of #4 SELECT - Use a SUM (DATEDIFF for days (dd) and multiple times room_rate FROM ====== (SELECT FROM room) AS 'Chain Average' FROM property p INNER JOIN building b ON PK = FK INNER JOIN room r ON PK= FK WHERE r.list_status = 'A' GROUP BY all fields listed in 1st SELECT other than AVG & SubQuery HAVING --reuse the 'Property Average' alias to be greater than and equal -- to 'Chain Average' (Subquery alias) View the number of nights guests are staying for each reservation WHERE the reservation is not cancelled (is NULL) JOIN JOIN WHERE GROUP BY */ ON ON ; --JOIN reservation and room_reservation - ON their PK to FK -- JOIN reservation_reservation and room -- ON their PK to FK -- where no cancelations -- fields beyond the aggregate in listed