Advanced Relational Database */ Outer Join Assignment w/ Variables */

1. Show all rooms for a given hotel. Show the customer name that is in the room if booked.

use YWGLH
Declare @HolidayInn int
set @HolidayInn = 1
select h.Name, r.Number, c.FirstName, c.LastName from
Rooms r
 left outer join Hotels h on h.HotelID = r.HotelID 
 left outer join Reservations re on r.RoomID = re.RoomID
 left outer join Customers c on re.CustomerID = c.CustomerID
 where h.HotelID = @HolidayInn

2. Show all rooms for a given hotel. Show the customer name that is in the room if booked.

Declare @DoubleBed varchar(50)
 Set @DoubleBed = 'DoubleBed'
Declare @Smoking varchar(50)
 Set @Smoking = 'Smoking'
select h.Name, h.Description, r.Number, r.RoomProperties, rs.StyleName from
Rooms r
 left outer join Hotels h on h.HotelID = r.HotelID
 left outer join RoomStyles rs on r.StyleID = rs.StylesID
 where rs.StyleName = @DoubleBed and r.RoomProperties = @Smoking

3. Which rooms are open for a given hotel on a given day. Order by price from cheapest to most expensive.

Declare @CheckIn datetime
 Set @CheckIn = '20110304 00:00:00'
Declare @CheckOut datetime
Set @CheckOut = '20110305 23:59:59'
select  h.Name, r.Number, re.CheckIn, re.CheckOut, ra.Cost from
Reservations re
 left outer join Rooms r on re.RoomID = r.RoomID
 left outer join Hotels h on r.HotelID = h.HotelID
 left outer join Rates ra on r.StyleID = ra.StylesID
 where re.CheckIn between @CheckIn and @CheckOut
 order by ra.Cost desc