/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET NAMES utf8 */; /*!50503 SET NAMES utf8mb4 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

-- Dumping database structure for delight_hotels DROP DATABASE IF EXISTS `delight_hotels`; CREATE DATABASE IF NOT EXISTS `delight_hotels`; USE `delight_hotels`;

-- Dumping structure for table delight_hotels.guest DROP TABLE IF EXISTS `guest`; CREATE TABLE IF NOT EXISTS `guest` ( `GUEST_ID` int(11) NOT NULL AUTO_INCREMENT, `GUEST_FNAME` varchar(50) NOT NULL, `GUEST_LNAME` varchar(50) NOT NULL, `GUEST_EMAIL` varchar(50) NOT NULL, `GUEST_PHONE` varchar(50) NOT NULL, `GUEST_ST_ADDRESS` varchar(50) NOT NULL, `GUEST_SUBURB` varchar(50) NOT NULL, `GUEST_STATE` varchar(50) NOT NULL, `GUEST_COUNTRY` varchar(50) NOT NULL, PRIMARY KEY (`GUEST_ID`) ) AUTO_INCREMENT=7;

-- Dumping data for table delight_hotels.guest: ~6 rows (approximately) DELETE FROM `guest`; INSERT INTO `guest` (`GUEST_ID`, `GUEST_FNAME`, `GUEST_LNAME`, `GUEST_EMAIL`, `GUEST_PHONE`, `GUEST_ST_ADDRESS`, `GUEST_SUBURB`, `GUEST_STATE`, `GUEST_COUNTRY`) VALUES (1, 'Donald', 'Trump', 'preseident@usa.gov', '+1 202 456 2121', '1600 Pennsylvania Avenue', 'Washington', 'DC', 'USA'), (2, 'Scott', 'Mirrison', 'contact@pm.gov.au', '+61 2 1234 5678', '5 Aelaide Ave', 'Deakin', 'ACT', 'Australia'), (3, 'Jacinda', 'Ardern', 'jacinda@parliament.nz', '+64 9 845 1919', '658 New North Rd', 'Morningside', 'Auckland', 'New Zealand'), (4, 'Boris', 'Johnson', 'johnson.mp@parliament.uk', '+44 20 7219 4682', '10 Downing St', 'Westminster', 'London', 'United Kkingdom'), (5, 'Nicole', 'Kidman', 'contact@nicole.com.au', '+61 2 3493 5683', '1 Aldred St', 'Milsons Point', 'NSW', 'Australia'), (6, 'Dusin', 'Martin', 'dustin@yahoo.com.au', '+61 2 1057 2397', '70 Coppin St', 'Richmond', 'VIC', 'Australia');

-- Dumping structure for table delight_hotels.hotel DROP TABLE IF EXISTS `hotel`; CREATE TABLE IF NOT EXISTS `hotel` ( `HTL_ID` int(11) NOT NULL AUTO_INCREMENT, `HTL_NAME` varchar(50) NOT NULL, `HTL_SUBURB` varchar(50) NOT NULL, `HTL_CITY` varchar(50) NOT NULL, `HTL_COUNTRY` varchar(50) NOT NULL, `HTL_STAR_RATING` int(11) NOT NULL, PRIMARY KEY (`HTL_ID`) ) AUTO_INCREMENT=8;

-- Dumping data for table delight_hotels.hotel: ~7 rows (approximately) DELETE FROM `hotel`; INSERT INTO `hotel` (`HTL_ID`, `HTL_NAME`, `HTL_SUBURB`, `HTL_CITY`, `HTL_COUNTRY`, `HTL_STAR_RATING`) VALUES (1, 'Crown Towers', 'Southbank', 'Melbourne', 'Australia', 5), (2, 'Skyways Hotel', 'Airport West', 'Melbourne', 'Australia', 3), (3, 'Beachside Apartments', 'Bonbeach', 'Melbourne', 'Australia', 4), (4, 'Quay Peth', 'Perth', 'Perth', 'Australia', 4), (5, 'Four Points by Sheratron', 'Perth', 'Perth', 'Australia', 5), (6, 'Holiday Inn Express', 'Clarke Quay', 'Singapore', 'Singapore', 5), (7, 'Joglo Vila', 'Denpasar', 'Bali', 'Indonesia', 3);

-- Dumping structure for table delight_hotels.reservation DROP TABLE IF EXISTS `reservation`; CREATE TABLE IF NOT EXISTS `reservation` ( `RES_ID` int(11) NOT NULL AUTO_INCREMENT, `GUEST_ID` int(11) NOT NULL, `RES_CHECKIN` date NOT NULL, `RES_CHECKOUT` date NOT NULL, `RES_BOOKING_TIME` datetime NOT NULL, PRIMARY KEY (`RES_ID`), KEY `FK_res_guest` (`GUEST_ID`), CONSTRAINT `FK_res_guest` FOREIGN KEY (`GUEST_ID`) REFERENCES `guest` (`GUEST_ID`) ) AUTO_INCREMENT=8;

-- Dumping data for table delight_hotels.reservation: ~7 rows (approximately) DELETE FROM `reservation`; INSERT INTO `reservation` (`RES_ID`, `GUEST_ID`, `RES_CHECKIN`, `RES_CHECKOUT`, `RES_BOOKING_TIME`) VALUES (1, 1, '2019-09-29', '2019-10-02', '2019-09-01 17:14:00'), (2, 2, '2019-10-12', '2019-10-16', '2019-10-10 10:18:55'), (3, 1, '2019-10-10', '2019-10-14', '2019-10-01 10:19:40'), (4, 3, '2020-01-31', '2020-02-02', '2019-12-27 15:21:02'), (5, 4, '2019-06-05', '2019-06-06', '2019-02-15 08:22:16'), (6, 5, '2019-09-24', '2019-09-28', '2018-09-28 09:24:04'), (7, 4, '2020-01-30', '2020-02-01', '2019-11-03 13:29:15');

-- Dumping structure for table delight_hotels.room DROP TABLE IF EXISTS `room`; CREATE TABLE IF NOT EXISTS `room` ( `RM_ID` int(11) NOT NULL AUTO_INCREMENT, `HTL_ID` int(11) NOT NULL, `RM_TYPE` varchar(50) NOT NULL, `RM_NUMBER` int(11) NOT NULL, `RM_NUM_BEDS` int(11) NOT NULL DEFAULT '1', `RM_DAILY_RATE` int(11) NOT NULL, PRIMARY KEY (`RM_ID`), KEY `FK_room_hotel` (`HTL_ID`), CONSTRAINT `FK_room_hotel` FOREIGN KEY (`HTL_ID`) REFERENCES `hotel` (`HTL_ID`) ) AUTO_INCREMENT=17;

-- Dumping data for table delight_hotels.room: ~16 rows (approximately) DELETE FROM `room`; INSERT INTO `room` (`RM_ID`, `HTL_ID`, `RM_TYPE`, `RM_NUMBER`, `RM_NUM_BEDS`, `RM_DAILY_RATE`) VALUES (1, 1, 'Standard', 106, 1, 160), (2, 1, 'Executive', 215, 3, 240), (3, 2, 'Deluxe', 8, 2, 120), (4, 2, 'Studio', 125, 4, 180), (5, 3, 'Executive', 512, 2, 185), (6, 4, 'Studio', 12, 5, 200), (7, 4, 'Executive Studio', 430, 6, 270), (8, 4, 'Standard', 220, 2, 140), (9, 5, 'Executive Studio', 620, 4, 300), (10, 5, 'Deluxe', 310, 2, 210), (11, 6, 'Standard', 5, 2, 85), (12, 6, 'Executive', 13, 1, 140), (13, 6, 'Studio', 11, 2, 130), (14, 6, 'Standard', 8, 4, 120), (15, 7, 'Standard', 29, 1, 40), (16, 7, 'Deluxe', 110, 2, 75);

-- Dumping structure for table delight_hotels.room_booking DROP TABLE IF EXISTS `room_booking`; CREATE TABLE IF NOT EXISTS `room_booking` ( `RES_ID` int(11) NOT NULL, `RM_ID` int(11) NOT NULL, `RMBK_NUM_ADULTS` int(11) NOT NULL, `RMBK_NUM_CHILD` int(11) NOT NULL, `RMBK_DAILY_RATE` int(11) NOT NULL, PRIMARY KEY (`RES_ID`,`RM_ID`), KEY `FK_rmbk_hotel_room` (`RM_ID`), CONSTRAINT `FK_rmbk_reservation` FOREIGN KEY (`RES_ID`) REFERENCES `reservation` (`RES_ID`), CONSTRAINT `FK_room_booking_room` FOREIGN KEY (`RM_ID`) REFERENCES `room` (`RM_ID`) );

-- Dumping data for table delight_hotels.room_booking: ~9 rows (approximately) DELETE FROM `room_booking`; INSERT INTO `room_booking` (`RES_ID`, `RM_ID`, `RMBK_NUM_ADULTS`, `RMBK_NUM_CHILD`, `RMBK_DAILY_RATE`) VALUES (1, 2, 2, 1, 230), (2, 5, 1, 1, 190), (3, 15, 1, 0, 40), (3, 16, 2, 0, 80), (4, 14, 2, 1, 125), (5, 1, 1, 0, 150), (6, 12, 1, 0, 140), (6, 13, 2, 0, 115), (7, 14, 3, 1, 130);

/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */; /*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

In this assignment, you are supplied a simple dataset for which you will write SQL queries. You will be using the well-known MySQL RDBMS for this task.

From the file DelightHotels.sql data file. Import this above file into your MySQL installation by following the instructions on Interact site.

The data file contains a schema and some sample data about bookings at several hotels which are part of the International Delight Hotels group. You can find records of some hotels, guests and their reservations. Note that guests make reservations at a hotel, with each reservation made up of one or more room bookings. All hotel rooms have an advertised rate, but actual booking cost varies depending on any discounts applied or partner commissions added.

Write and execute SQL statement(s) to complete each of the following tasks. Copy your SQL statements into the assignment document. Secondly, include the screenshots of results obtained by executing those statements on the DBMS. Typing the results of SQL statements is NOT acceptable.

1. List the names and addresses of all Australian guests, alphabetically ordered by name.
2. What would be the total revenue per night from all 'Studio' rooms if all rooms are occupied and booked at their advertised rate?
3. How many bookings fall in October 2019?
4. List names of guests who have booked a room at the Beachside Apartments on 13th October 2019.
5. List the room numbers that are currently occupied at Holiday Inn Express.
6. List the hotel names and the total number of rooms in each hotel in Indonesia.
7. Create a 'view' containing the names of all hotels worldwide and their average room rate. Sort the view by increasing price. Finally display the view.
8. Find out how many children are accompanying Boris Johnson in each of his stays? Show the hotel names and corresponding number of children.
9. Find phone numbers of all the guests who have or had a booking at Crown Towers in room number 215.
10. Create a separate table with the same structure as the Reservations table to hold archive records. Using INSERT statement, copy the records of all past bookings (that ended before today) from the Reservation table to the archive table. Then delete all archived bookings from the Reservation table. Note: You can recover the original data by reimporting the sql data file.

Additional challenge for fun: Find out if any rooms have overlapping bookings (which is a data integrity problem). Show the hotel name, guest name, room number, check-in and checkout dates for such cases.