I need help with this query in SQL. Here are the tables: breakfast ( bType: varc
ID: 3822470 • Letter: I
Question
I need help with this query in SQL.
Here are the tables:
breakfast ( bType: varchar(32),
HotelID: int,
Description varchar(128),
bPrice: decimal(5,2),
primary key (bType, HotelID),
foreign key (HotelID) references hotel (HotelID));
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));
hotel ( HotelID: int,
Street: varchar(50),
City: varchar(50),
State: char(2),
ZIP: char(10),
Country: varchar(20),
Primary key(HotelID)
);
Query: For a given time period(begin date and end date) compute the highest rated breakfast type across all hotels.
Explanation / Answer
The beginning date and end date is not given in any of the tables, so it's not possible to get these dates. However, to get the highest rated breakfast simply use a this query
SELECT bType, HotelId, MAX(bprice) from breakfast
This query will return bType and HotelId of the highest rated breakfast
Or you can use joins to get hotel details as well
SELECT t1.street, t2.btype, t2.hotelid, max(t2.bprice) from hotel t1 Inner join breakfast t2 on t1.id = t2.hotelid