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 Resources section on Interact site, download the file DelightHotels sql data file Import this 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 List the names and addresses of all Australian guests, alphabetically ordered by name What would be the total revenue per night from all 'Studio' rooms if all rooms are occupied and booked at their advertised rate How many bookings fall in October 2019 List names of guests who have booked a room at the Beachside Apartments on 13th October 2019 List the room numbers that are currently occupied at Holiday Inn Express List the hotel names and the total number of rooms in each hotel in Indonesia 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 Find out how many children are accompanying Boris Johnson in each of his stays Show the hotel names and corresponding number of children Find phone numbers of all the guests who have or had a booking at Crown Towers in room number 215 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 here is the code 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

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Jul 27, 2024

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

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 Resources section on Interact site, download the file DelightHotels.sql data file. Import this 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.

List the names and addresses of all Australian guests, alphabetically ordered by name.
What would be the total revenue per night from all 'Studio' rooms if all rooms are occupied and booked at their advertised rate?
How many bookings fall in October 2019?
List names of guests who have booked a room at the Beachside Apartments on 13th October 2019.
List the room numbers that are currently occupied at Holiday Inn Express.
List the hotel names and the total number of rooms in each hotel in Indonesia.
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.
Find out how many children are accompanying Boris Johnson in each of his stays? Show the hotel names and corresponding number of children.
Find phone numbers of all the guests who have or had a booking at Crown Towers in room number 215.
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.
here is the code
/*!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 */;