Given the following tables in MySQL, write a query to find the highest rated bre

Question

Given the following tables in MySQL, write a query to find the highest rated breakfast type across all hotels for a given range of time (a start and finish date).

CREATE TABLE Hotel (
   hotel_id int AUTO_INCREMENT PRIMARY KEY,
   hotel_name varchar(50) NOT NULL,
   street varchar(50) NOT NULL,
   city varchar(15) NOT NULL,
   state varchar(20) NOT NULL,
   postal_code varchar(9) NOT NULL,
country varchar(50) NOT NULL
);

CREATE TABLE Breakfast (
   btype varchar(20) NOT NULL,
   description varchar(80) NOT NULL,
   bprice real NOT NULL,
   hotel_id int NOT NULL,
   PRIMARY KEY(hotel_id, btype),
   FOREIGN KEY(hotel_id) REFERENCES Hotel(hotel_id) ON DELETE CASCADE
);

CREATE TABLE Customer (
   cid int AUTO_INCREMENT PRIMARY KEY,
   email varchar(55) UNIQUE NOT NULL,
   address varchar(75) NOT NULL,
   phone_no varchar(15) NOT NULL,
   name varchar(50) NOT NULL,
   password varchar(128) NOT NULL
);

CREATE TABLE Reservation (
   invoice_no int AUTO_INCREMENT PRIMARY KEY,
   reservation_date datetime NOT NULL,
   cid int NOT NULL,
   cnumber varchar(16) NOT NULL,
   total real NOT NULL,
   FOREIGN KEY(cid) REFERENCES Customer(cid),
   FOREIGN KEY(cnumber) REFERENCES Credit_Card(cnumber)
);

CREATE TABLE Reservation_Room (
   id int AUTO_INCREMENT PRIMARY KEY,
   out_date datetime,
   in_date datetime NOT NULL,
   invoice_no int NOT NULL,
   room_no int NOT NULL,
   hotel_id int NOT NULL,
   FOREIGN KEY(invoice_no) REFERENCES Reservation(invoice_no) ON DELETE CASCADE,
   FOREIGN KEY(hotel_id, room_no) REFERENCES Room(hotel_id, room_no) ON DELETE CASCADE
);

CREATE TABLE Reservation_Breakfast (
   hotel_id int NOT NULL,
   btype varchar(20) NOT NULL,
   invoice_no int NOT NULL,
   FOREIGN KEY(hotel_id, btype) REFERENCES Breakfast(hotel_id, btype) ON DELETE CASCADE,
   FOREIGN KEY(invoice_no) REFERENCES Reservation(invoice_no) ON DELETE CASCADE
);

CREATE TABLE Breakfast_Review (
   reviewid int PRIMARY KEY,
   btype varchar(20) NOT NULL,
   hotel_id int NOT NULL,
   cid int DEFAULT 1 NOT NULL,
   FOREIGN KEY(reviewid) REFERENCES Review(reviewid),
   FOREIGN KEY(hotel_id, btype) REFERENCES Breakfast(hotel_id, btype),
   FOREIGN KEY(cid) REFERENCES Customer(cid)
);

Explanation / Answer

Please give thumbs up, If it is helpful for you. Thankyou!!

Query:

SELECT RB.btype
FROM Reservation_Breakfast RB INNER JOIN Reservation R
ON RB.invoice_no = R.invoice_no
WHERE R.total = MAX(total)
R.reservation_date BETWEEN '2017-01-01' AND '2017-04-01';

DATE IS SAMPLE DATE IN WHERE CLAUSE , You can select a start and finish date by your own choice.

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.