Help finding the total income from a booking for a given hotel date

Databases
1

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’
2

Hi Sevim_WebDesign, welcome to the forum,

what happened when you tried it? ™

1 Like
3

didnt do anything at all

4

im not sure if i did something wrong with the coding but cant get it right tryed it five times

5

No error messages? A hang?

6

no error it wont even tell me if there is a error for this code :frowning:

7

Assuming the syntax is correct, you mean the query is not returning what you want it to.
What is the query returning?

8

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?

9

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

10

That too :slight_smile:

11

it is a booking for a hotel but im not sure if it the right way to do it because its not giving me the correct information about it.

12

i tryed every way but it wont let me get the correct information of the booking

13

And what is it giving you?

14

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.

15

what is the total income from bookings for the ‘Sofitel’ Hotel to date? this is what i need to do for it but im not sure if it is the correct one

16

How are the tables related to each other? Which field belongs to which table? Without that information, it is impossible to do anything

17

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

18 
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

19

i just wanted to find out how it looks like

20

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)