Help with hotel exercise

Databases
1

Hotel (hotelNo,hotelName,hotelAddress)
Room (hotelNo,roomNo,type,price)
Guest (guestNo,guestName,guestAddress)
Booking (hotelNo,guestNo,dateFrom,dateTo,roomNo)

Use subqueries instead of JOIN (inner or outer)

What is the percentage of lost income from unoccupied rooms at each hotel on
July 6 2008? Round to the nearest % using the ROUND function, and use || to
concatenate ‘%’ to your output

2

where’s your attempt to solve this?

we don’t do homework assignments here

however, we will help you to do them

so, let’s see your attempt

3

Well we could become a homework club couldn’t we? :wink:

4

Sorry about that. I was able to get the loss income for each room but dont know how to turn it into percentage.

SELECT hotelno, SUM(price) FROM room r
WHERE roomno NOT IN
(SELECT roomno FROM booking b, hotel h
WHERE (datefrom <= DATE’2008-07-06’ AND
dateto >= DATE’2008-07-06’) AND
b.hotelno = h.hotelno)
GROUP BY hotelno;

5

may i ask which database system you ran this on?

sure doesn’t look like mysql