what is the total income from bookings for the ‘Sofitel’ Hotel to date?
i did this but im not sure if this is correct
SELECT SUM(price) FROM room
Where roomNo
(SELECT roomNo FROM booking, hotel
WHERE (dateFROM<=Current_Date And dateTo>=Current_Date) And
hotelName= ‘Sofitel’
The problem is in the first WHERE. There is not comparison symbol or anything. Still, not the way I’d do it
Instead of trying to use a subquery in your WHERE clause (which is not working because roomNo is not equal to anything, you should at least used the IN clause)… why haven’t tried to use joints?
I’m no SQL expert, but it looks like there might be a closing bracket missing in there somewhere - there’s definitely one less than there are opening brackets
You should idetentified your fields first. It is clear that price and roomNo belong to the table room.
It is not clear which table has the fields dateFROM and dateTo. I guess that hoteName comes from hotel but the relationships between the three tables are unclear.
Also, I’m guessing that you’re trying to list the price of every room.
hotel.hotelName as name, SUM(room.price) as Total FROM room
INNER JOIN booking ON booking.roomNo=room.roomNo
INNER JOIN hotel ON hotel.hotelNo=room.hoteNo
WHERE hotel.dateTo <= Currrent_Date and hotel.hotelName='Sofitel'
GROUP BY hotel.hotelName
I haven’t used dateFrom because I really don’t care when the people sings in, they pay when they leave. But this is only a point of view.
Still, this looks like homework and you should understand how tables relate to each other which I suspect is the point of the exercise
Well, as you can see, I’ve changed a bit my first query. I thought it would be nice to show the name of the hotel in the result.
Now you can try to answer questions like how much each guest has spent at Sofitel (similar question but I hope that you will know how to do this one yourself)