I need help with this query in SQL. Here are the tables: service( sType: varchar
ID: 3822477 • Letter: I
Question
I need help with this query in SQL.
Here are the tables:
service( sType: varchar(32),
HotelID: int,
sCost: decimal(5,2),
primary key(sType, 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 service type across all hotels.
Explanation / Answer
SQL query will be something like below, please modify the dates as per your date column structure into table:
Select h.HotelID, MAX(r.rating) AS HighestRating
From hotel h Left Join review r
ON h.HotelID = r.HotelID
Left Join service s
ON s.HotelID = r.HotelID
Where date BETWEEN Begin_Date and End_Date;