Menu

(Solved) : 40101 Set Oldcharactersetclient Charactersetclient 40101 Set Names Utf8 50503 Set Names Ut Q44098958 . . .

/*!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 202456 2121’, ‘1600 Pennsylvania Avenue’, ‘Washington’, ‘DC’,’USA’),
   (2, ‘Scott’, ‘Mirrison’, ‘contact@pm.gov.au’, ‘+61 21234 5678’, ‘5 Aelaide Ave’, ‘Deakin’, ‘ACT’, ‘Australia’),
   (3, ‘Jacinda’, ‘Ardern’, ‘jacinda@parliament.nz’, ‘+649 845 1919’, ‘658 New North Rd’, ‘Morningside’, ‘Auckland’, ‘NewZealand’),
   (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’, ‘+612 3493 5683’, ‘1 Aldred St’, ‘Milsons Point’, ‘NSW’,’Australia’),
   (6, ‘Dusin’, ‘Martin’, ‘dustin@yahoo.com.au’, ‘+61 21057 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-0117:14:00’),
   (2, 2, ‘2019-10-12’, ‘2019-10-16’, ‘2019-10-1010:18:55’),
   (3, 1, ‘2019-10-10’, ‘2019-10-14’, ‘2019-10-0110:19:40’),
   (4, 3, ‘2020-01-31’, ‘2020-02-02’, ‘2019-12-2715:21:02’),
   (5, 4, ‘2019-06-05’, ‘2019-06-06’, ‘2019-02-1508:22:16’),
   (6, 5, ‘2019-09-24’, ‘2019-09-28’, ‘2018-09-2809:24:04’),
   (7, 4, ‘2020-01-30’, ‘2020-02-01’, ‘2019-11-0313: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 whichyou will write SQL queries. You will be using the well-known MySQLRDBMS for this task.

From the file DelightHotels.sql data file. Import this abovefile into your MySQL installation by following the instructions onInteract site.

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

Write and execute SQL statement(s) to complete each ofthe following tasks. Copy your SQL statements into theassignment document. Secondly, include the screenshots of resultsobtained by executing those statements on the DBMS. Typing theresults 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 advertisedrate?
  3. How many bookings fall in October 2019?
  4. List names of guests who have booked a room at the BeachsideApartments on 13th October 2019.
  5. List the room numbers that are ‘currently’ occupied at HolidayInn Express.
  6. List the hotel names and the total number of rooms in eachhotel in Indonesia.
  7. Create a ‘view’ containing the names of all hotels worldwideand their average room rate. Sort the view by increasing price.Finally display the view.
  8. Find out how many children are accompanying Boris Johnson ineach of his stays? Show the hotel names and corresponding number ofchildren.
  9. Find phone numbers of all the guests who have or had a bookingat Crown Towers in room number 215.
  10. Create a separate table with the same structure as theReservations 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 allarchived bookings from the Reservation table. Note: You can recoverthe original data by reimporting the sql data file.

Additional challenge for fun: Find out if any roomshave overlapping bookings (which is a data integrity problem). Showthe hotel name, guest name, room number, check-in and checkoutdates for such cases.

Expert Answer

Answer to /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET NAMES utf8 */; /*!50503 SET NAMES utf8mb4…

OR


See what happens after you place this order.

Order a custom paper written from scratch according to your requirements

 

Place your order now