Create an XML document and an XML Schema for the Hotel Schema given in the Exercises at the end of Chapter 4. Now attempt to write XQuery expressions for Exercises 6.7 – 6.26.

6.7 List full details of all hotels.
6.8 List full details of all hotels in London.
6.10 List all double or family rooms with a price below £40.00 per night, in ascending order of price.
6.12 How many hotels are there?
6.13 What is the average price of a room?
6.14 What is the total revenue per night from all double rooms?
6.15 How many different guests have made bookings for August?
6.16 List the price and type of all rooms at the Grosvenor Hotel.
6.17 List all guests currently staying at the Grosvenor Hotel.
6.22 List the number of rooms in each hotel.
6.23 List the number of rooms in each hotel in London.

Could create one large XML file or one for each relation. For the latter case, could have:





H111
Grosvenor Hotel
London


H112
Hyde Park Hotel
London

…






R101
H111
S
75


R102
H111
D
129

…


Some sample XQuery expressions would be:

6.7 List full details of all hotels.

doc(“hotel_list.xml”)/HOTELLIST

6.8 List full details of all hotels in London.

doc(“hotel_list.xml”)//HOTEL/[city= “London”]

6.10 List all double or family rooms with a price below £40.00 per night, in ascending order of price.

FOR $R IN doc(“room_list.xml”)//ROOM
WHERE $R/price < 40 AND ($R/type = “D“ OR $R/type = “F“)
ORDER BY $R/price ASCENDING
RETURN $R

6.12 How many hotels are there?

count(doc(“hotel_list.xml”)/HOTELLIST/HOTEL/hotelNo)

6.13 What is the average price of a room?

avg(doc(“room_list.xml”)/ROOMLIST/ROOM/price)
6.14 What is the total revenue per night from all double rooms?

sum(doc(“room_list.xml”)/ROOMLIST/ROOM[type = “D“]/price)

6.15 How many different guests have made bookings for August?

LET $noGuests := count(distinct-values(doc(“guest_list.xml”)//guestNo))
WHERE (dateFrom <= 2004-08-01 AND dateTo >= 2004-08-01) OR
(dateFrom >= 2004-08-01 AND dateFrom <= 2004-08-31)
RETURN
$noGuests

6.16 List the price and type of all rooms at the Grosvenor Hotel.

FOR $R INdoc(“room_list.xml”)//ROOMLIST/ROOM
FOR $H INdoc(“hotel_list.xml”)//HOTELLIST/HOTEL
WHERE $R/hotelNo = $H/hotelNo AND
$H/hotelName = ‘Grosvenor Hotel’
RETURN

$R/price
$R/type


6.17 List all guests currently staying at the Grosvenor Hotel.

FOR $G INdoc(“guest_list.xml”)//GUESTLIST/GUEST
FOR $B INdoc(“booking_list.xml”)//BOOKINGLIST/BOOKING
FOR $H INdoc(“hotel_list.xml”)//HOTELLIST/HOTEL
WHERE $G/guestNo = $B/guestNo AND
$B/hotelNo = $H/hotelNo AND
$H/hotelName = ‘Grosvenor Hotel’ AND
dateFrom <= CURRENT_DATE AND
dateTo >= CURRENT_DATE AND
RETURN
$G

(replace CURRENT_DATE with today’s date)
6.22 List the number of rooms in each hotel.

FOR $hotelNo INdistinct-values(doc(“room_list.xml”)//hotelNo)
LET $noRooms := count(doc(“room_list.xml”)//ROOM[hotelNo = $hotelNo])
RETURN

$hotelNo
$noRooms


6.23 List the number of rooms in each hotel in London.

FOR $H INdoc(“hotel_list.xml”)//HOTELLIST/HOTEL
LET $rooms := FOR $R INdoc(“room_list.xml”)//ROOMLIST/ROOM
WHERE $R/hotelNo = $H/hotelNo AND
$H/city = ‘London’
RETURN $R
LET $noRooms := count($rooms)
RETURN

$hotelNo
$noRooms