3. Create the following Views

: a. (5 points) Create a view of hotelNo, hotelName and the average price of a room in the hotel.

b. (5 points) Create a view of hotelNo, hotelName and number of bookings at each hotel.

c. (5 points) Create a view of guestNo, guesName and number of bookings made by him/her.

d. (5 points) Create a view of all the rooms that werent booked in the month of April16.

4. Alter the following tables:

a. (5 points) Remove the city column from Guest table.

b. (5 points) Set the default to N for returningGuest in Guest table.

c. (5 points) Add a column noOfRooms to Hotel table.

d. (5 points) Remove the default price from Room table.

DROP SCHEMA IF EXISTS HotelSchema;

CREATE SCHEMA HotelSchema;

USE HotelSchema;

CREATE TABLE Hotel(hotelNo VARCHAR(3), hotelName VARCHAR(20), city VARCHAR(20)); CREATE TABLE Guest (guestNo VARCHAR(4), guestName VARCHAR(30), guestAddress VARCHAR(50), city VARCHAR(20)); CREATE TABLE Room (roomNo INT(3), hotelNo VARCHAR(3), roomtype VARCHAR(25), price FLOAT); CREATE TABLE Booking (hotelNo VARCHAR(3), guestNo VARCHAR(4), dateFrom date, dateTo date, roomNo INT(3));

INSERT INTO Hotel VALUES('H01', 'Hilton', 'Oklahoma City'); INSERT INTO Hotel VALUES('H02', 'Hilton Garden Inn', 'Oklahoma City'); INSERT INTO Hotel VALUES('H03', 'Mariott', 'Oklahoma City'); INSERT INTO Hotel VALUES('H04', 'Comfort Suites', 'Oklahoma City'); INSERT INTO Hotel VALUES('H05', 'Sheraton', 'New York City'); INSERT INTO Hotel VALUES('H06', 'Hilton', 'San Jose'); INSERT INTO Hotel VALUES('H07', 'Holiday Inn', 'Seattle'); INSERT INTO Hotel VALUES('H08', 'Embassy Suites', 'Dallas'); INSERT INTO Hotel VALUES('H09', 'Mariott', 'Dallas'); INSERT INTO Hotel VALUES('H10', 'Hyatt', 'Oklahoma City');

/*SELECT * FROM Hotel;*/

INSERT INTO Guest VALUES('G001', 'Jane Doe', '1506 Chambers St', 'Oklahoma City'); INSERT INTO Guest VALUES('G002', 'Harry Patel', '23 Drury Ln', 'Oklahoma City'); INSERT INTO Guest VALUES('G003', 'Daniel Ross', '450 James St', 'Oklahoma City'); INSERT INTO Guest VALUES('G004', 'Helen Bush', '450 James St', 'St. Louis'); INSERT INTO Guest VALUES('G005', 'Susan Chase', '1301 4th St', 'New York City'); INSERT INTO Guest VALUES('G006', 'Samantha Drew', '1499 Fioli Loop', 'San Jose'); INSERT INTO Guest VALUES('G007', 'Benn Franklin', '99 23rd St', 'Seattle'); INSERT INTO Guest VALUES('G008', 'Robert Grove', '24 Baycharter Blvd', 'Dallas'); INSERT INTO Guest VALUES('G009', 'Mary Harker', '340 Jenkins Cr', 'Dallas'); INSERT INTO Guest VALUES('G010', 'Yana Krum', '280 Central Pkwy', 'Oklahoma City');

/*SELECT * FROM Guest;*/

INSERT INTO Room VALUES(701, 'H04', 'King', 250); INSERT INTO Room VALUES(202, 'H03', 'King', 200); INSERT INTO Room VALUES(403, 'H04', 'Double Queen', 175); INSERT INTO Room VALUES(205, 'H10', 'Double Queen', 159); INSERT INTO Room VALUES(107, 'H02', 'Double Twin', 99); INSERT INTO Room VALUES(107, 'H06', 'Single Queen', 169); INSERT INTO Room VALUES(108, 'H04', 'Double Queen', 79); INSERT INTO Room VALUES(209, 'H03', 'Suite King', 199); INSERT INTO Room VALUES(710, 'H01', 'Suite Single Queen', 159); INSERT INTO Room VALUES(710, 'H04', 'Suite Single Queen', 89); INSERT INTO Room VALUES(402, 'H04', 'King', 129); INSERT INTO Room VALUES(605, 'H04', 'Suite Double Queen', 89);

/*SELECT * FROM Room;*/

INSERT INTO Booking VALUES('H04', 'G006', 20160404, 20160406, 701); INSERT INTO Booking VALUES('H03', 'G001', 20160516, 20160526, 202); INSERT INTO Booking VALUES('H04', 'G003', 20160113, 20160115, 403); INSERT INTO Booking VALUES('H10', 'G005', 20160820, 20160828, 205); INSERT INTO Booking VALUES('H02', 'G005', 20160401, 20160406, 107); INSERT INTO Booking VALUES('H06', 'G003', 20160823, 20160825, 107); INSERT INTO Booking VALUES('H04', 'G003', 20160404, 20160406, 108); INSERT INTO Booking VALUES('H03', 'G002', 20160516, 20160526, 209); INSERT INTO Booking VALUES('H01', 'G010', 20160810, 20160815, 710); INSERT INTO Booking VALUES('H04', 'G010', 20160404, 20160504, 710);

/*SELECT * FROM Booking;*/