I need help with this SQL query. Here are the tables: review( ReviewID: int, rat
ID: 3822012 • Letter: I
Question
I need help with this SQL query.
Here are the tables:
review( ReviewID: int,
rating: int,
TextComment: text,
room_no: int,
bType: varchar(32),
sType: varchar(32),
HotelID: int,
primary key(ReviewID),
foreign key (bType) references breakfast (bType),
foreign key (Room_no) references room (Room_no),
foreign key (HotelID) references hotel (HotelID),
foreign key (sType) references service (sType));
reservation( InvoiceNo: int,
ResDate: date,
TotalAmt: decimal(5,2)
CID: int,
Room_no: int,
HotelID:int,
InDate: date,
OutDate: date,
NoOfDays: int,
Cnumber: varchar(16),
bType: varchar(32),
sType: varchar(32),
primary key(InvoiceNo),
foreign key(CID) references customer(CID),
foreign key(Cnumber) references creditcard (Cnumber),
foreign key(HotelID) references hotel (HotelID),
foreign key(bType) refercens breakfast (bType),
foreign key(sType) referecnes service (sType),
foreign key(Room_no) referecnes room (Room_no));
Room( Room_no: int,
HotelID: int,
Price: decimal(5,2),
Capacity: int,
Floor_no: int,
Description: varchar(255),
Type: enum('standard','double','deluxe','suite'),
primary key (Room_no, HotelID),
foreign key (HotelID) references hotel (HotelID));
Query:
For a given time period(begin date and end date) compute the highest rated room type for each hotel.
Explanation / Answer
SELECT Room.Type,Room.HotelID,max(Review.Rating)
FROM Review, Reservation, Room
WHERE Reservation.InDate BETWEEN(BeginDate , EndDate)
AND Reservation.OutDate BETWEEN((BeginDate , EndDate)
AND Reservation.HotelID = Review.HotelID
AND Reservation.Room_No = Review.Room_No
AND Review.HotelID = Room.HotelID
AND Review.Room_No = Room.Room_No
GROUP BY Room.HotelID;