I need help with this query in SQL. Here are the tables: service( sType: varchar
ID: 3823122 • 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)
);
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));
Query: For a given time period(begin date and end date) compute the highest rated service type across all hotels.
Explanation / Answer
Query: For a given time period(begin date and end date) compute the highest rated service type across all hotels.
Assume :
begin date : '1/10/2009'
end date : '30/10/2009'
Select top 1
rev.sType as servicetype,
rev.rating as highestrating
From review as rev
INNER JOIN reservation res on res.sType = rev.sType
Where res.ResDate >= CONVERT(DATE,'1/10/2016') AND res.ResDate <= CONVERT(DATE,'30/10/2016')
order by rev.rating DESC