Question
I need help running SQL queries based on the table picture which i have included down below... thanks
QUESTION : Write a select statement to determine the RackRate and nightly tax for room number 302 at Sunridge for any date between Jun 26 and July 6.
** this is the code i used, but im not sure if it is correct or not.. please help
SELECT RACKRATE.RackRate, TAXRATE.RoomTaxRate,TAXRATE.SalesTaxRate
From HOTEL
JOIN RACKRATE
ON HOTEL.HotelID = RACKRATE.HotelID
JOIN ROOM
on ROOM.HotelID = HOTEL.HotelID
JOIN TAXRATE
on TAXRATE.TaxLocationID = Hotel.TaxLocationID
WHERE HOTEL.HotelID=2100 AND ROOM.RoomNumber= '302' AND RACKRATE.RackRateBegin < '2017-06-26' AND RACKRATE.RackRateEnd > '2017-07-06'
RESERVATION equines has- PK CreditCardD smallint GUEST ROOMTYPE PK nt Smal FK 1 GuestID smallint ReservationDate date PK Room smallint CCType varchar (5 Reservationstatus char(1 Guest First varchar(20 CCNumber varchar (16 Reservation Comments varchar (200 Guest Last varchar(20 CCCompany varchar (40) FK1 CreditCardID RTDescription varchar (200 smallint Guest Address varchar(30 ccCard Holder varchar(40) GuestAddress2 varchar (10) CCExpiration smalldatetime Res Status: CHECK R,A,C,X has GuestCity varchar(20 GuestState char(2) DISCOUNT Guest PostalCode char (10 RACKRATE PK smallint Guestcountry varchar(20 has PK BackBateID smallint Guest Phone varchar(20 Guest Ema varchar (30) escription varchar (50) FK1 oomTypeID nt GuestComments. varchar (200 Smal tains Discount Expiration date FK2 HotellD smallint DiscountRules varchar (100 Rack Rate small money DiscountPercen decimal (4,2) ROOM RackRate Begin date DiscountAmoun small money IPK BeamID smallint Rack Rate End date RackRateDescription varchar(200 has Room Numbe varchar(5) RoomDescription varchar (200) has Room Smoking bit otellD is NOT an Identity FOLIO RoomBedConfiguration char (2 HOTEL assigned.H FK3 HotelID Smal K FolioID. has FK 4 RoomTypeID nt IPK Hotellp smallint Smal ed Config K1 ReservationID nt Sma GuestID(TRIGGER) nt HotelName CHECK varchar(30 BILLING K2 RoomID smallint HotelAddress varchar(30 K, Q, F,2Q,2K,2F Quoted ate HotelCity varchar(20 small money PK FolloBillinglD Checkin Date smalldatetime HotelState char (2) ha Nights tinyint Hotel Country varchar(20) FK1 FoliollD smallint Status char (1) Hotel PostalCode char (10 FK2 BillingCategoryID nt Comments varchar (200 HotelStarRating char (1) BillingDescription char (30 K3 Discount ID Hotel PictureLink varchar (100) nt Sma Billing Amount small money FK 1 Tax LocationID Smal nt BillingltemQty tinyint Status: CHECK Discount: DEFAULT has BillingltemDate date R,A,C, X located in PAYMENT PaymentID IDENTITY (8000, PK BaymentLD TAXRATE smallint BILLINGCATEGORY FK1 FoliolID nt Sma PaymentDate date TaxDescription varchar (30 PaymentAmount Billing CatDescription varchar (30) small money RoomTaxRate decimal (6,4 Payment Comments varchar (200 Ver 1.1 6/5/2017 BillingCat Taxable bit decimal (6,4) Sales TaxRate
Explanation / Answer
SELECT AVG(RACKRATE.RackRate), AVG(TAXRATE.RoomTaxRate+TAXRATE.SalesTaxRate) AS NightlyTax -- Taking average as we need nightly data, i.e. just for one night of the room
FROM HOTEL
INNER JOIN RACKRATE
ON HOTEL.HotelID = RACKRATE.HotelID
AND HOTEL.HOTELNAME = 'Sunridge'
INNER JOIN ROOM
on ROOM.HotelID = HOTEL.HotelID
AND ROOM.RoomNumber = '302' --Get data for room number 302
AND RACKRATE.RackRateBegin BETWEEN '2017-06-26' AND '2017-07-06' -- Get data for any date between Jun 26 and July 6, we will consider only begin date because begin and end date will give data for range and not nightly charge..
INNER JOIN TAXRATE
on TAXRATE.TaxLocationID = Hotel.TaxLocationID
