The following tables form part of a database held in a relational DBMS:

Hotel (hotelNo, hotelName, city)

Room (roomNo, hotelNo, type, price, city)

Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo)

Guest (guestNo, guestName, guestAddress, city)

where Hotel contains hotel details, Room contains room details for each hotel, Booking contains details of bookings, and Guest contains guest details.

Set operators: (use MySQL)

(6 points) List all the cities where a guest lives in or there is a hotel. Use set operators.

(6 points) List all the cities where a guest lives in and there is a hotel. (Note: There is no Intersect operator in MySQL.)

(6 points) List all the cities where a guest lives in but there is no hotel. (Note: There is no Except operator in MySQL.)

(10 points) Find hotels in which rooms are priced higher than the price of at least one room at Comfort Suites. Use ANY, SOME or ALL. Do not list duplicate hotels.

(10 points) List all details of rooms in Oklahoma City. Use EXISTS.

(10 points) List all details of rooms not in Oklahoma City. Use NOT EXISTS.

(10 points) Insert one new record into each of the tables above. (Hint: 4 different queries)

(10 points) Update the price of all rooms by 5% increase in price.

(10 points) Assume a new table has been created with tablename:

RoomCount (hotelNo, hotelName, numOfRooms, avgPrice).

Create the table using the following query:

CREATE TABLE RoomCount (hotelNo VARCHAR(3), hotelName VARCHAR(20), numberOfRooms INT(3), avgPrice FLOAT);

Populate the new table using the tables given above. (Note: Do not list hotels with 0 rooms.)

(10 points) Update the price of all double rooms in Oklahoma City by a 3% decrease in price.

(10 points) Delete all the bookings older than March, 2016 (Hint: Do not delete bookings that end after March, 2016).

(10 points) Assume a new table has been created with tablename:

GuestBookingCount (guestNo, guestName, noOfBookings).

Create the table using the following query:

CREATE TABLE GuestBookingCount (guestNo VARCHAR(4), guestName VARCHAR(30), noOfBookings INT(3));

Populate the new table from the tables given above. (Note: Also list the guests who have 0 bookings.)

(10 points) Assume a new table has been created with tablename:

HotelRoomCountByType (hotelNo, hotelName, roomtype, numberOfRooms).

Create the table using the following query:

CREATE TABLE HotelRoomCountByType (hotelNo VARCHAR(3) , hotelName VARCHAR(20), roomtype VARCHAR(25) , numberOfRooms INT(3));

Populate that table from the tables given above.

(10 points) Delete all guest information from Guest table and Booking table of all the guests in Dallas. (Hint: You will need 2 queries to do this.) Which table will you delete the information from first?

(10 points) Delete all rows from Booking table.

(10 points) Update the room type to Deluxe King where price is greater than $150 and also increase its price by 5%.

*****************Hotel*************************

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;*/